ProbablyOriginally Posted by sandy666
do ya have : Attention deficit hyperactivity disorder (ADHD) ???
it is fixer's fault - he is driving me mad!
Probablydo ya have : Attention deficit hyperactivity disorder (ADHD) ???
it is fixer's fault - he is driving me mad!
easy, easy, this is a patience test
sandy
I know you know but I forgot my Crystal Ball and don't know what you know also I may not remember what I did weeks, months or years ago so answer asap. Thinking doesn't hurt
It is character building.
Actually, you are good at geussing what he wants...
I will post just once more now in the main Thread , and then go and break some more rocks for relaxation for the rest of the day..
C ya tomorrrow
Another attempt to geuss what fixer is askig for from here:
https://excelfox.com/forum/showthrea...ll=1#post13256
Code:Sub OpenTxtFiles_ValuesToBeSeperatedIntoExcelCells() ' Comma seperated values text files Call OpenA____SeperatedValuesTextFile("CommaSeperatedValues.csv", ",") Call OpenA____SeperatedValuesTextFile("CommaSeperatedValues.txt", ",") End Sub Sub OpenA____SeperatedValuesTextFile(ByVal Filname As String, ByVal Seprator As String) Rem 1 Get text file as long string. Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function Dim PathAndFileName As String, TotalFile As String Let PathAndFileName = ThisWorkbook.Path & "\" & Filname ' Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundemental type data input... TotalFile = Space(LOF(FileNum)) '....and wot recives it hs to be a string of exactly the right length Get #FileNum, , TotalFile Close #FileNum Rem 2 Put values in Excel Dim Ws1 As Worksheet Set Ws1 = ThisWorkbook.Worksheets.Item(1) Ws1.Cells.ClearContents '2b) Split Total File text into a 1 Dimensional array into rows Dim RwTxt() As String: Let RwTxt() = Split(TotalFile, vbCr & vbLf, -1, vbBinaryCompare) Dim Clms() As String Let Clms() = Split(RwTxt(0), Seprator, -1, vbBinaryCompare) ' This will be the first row of data. Here we are doing it just to gat the column count. In the loop below, we will use it for every row, including initially this first row. We need it below to allow us to access each value seperately seperated via the seprator, seprator Dim HedClmsCnt As Long: Let HedClmsCnt = UBound(Clms) + 1 ' +1 is required , as , by default , a 1Dimensional array from split has first element indicie of 0 , so Ubound will be 1 less than the number of elements Dim arrOut() As String ' I must make this dynamic, since i must use the TReDim method to size it. This is because the Dim statement will not accept variables or non static values: It omly accepts actual integer hard coded numbers ReDim arrOut(1 To UBound(RwTxt) + 1, 1 To HedClmsCnt) ' +1 is required , as , by default , a 1Dimensional array from split has first element indicie of 0 , so Ubound will be 1 less than the number of elements Dim RwCnt As Long For RwCnt = 0 To UBound(RwTxt) '2c) Split each row into seperated values Let Clms() = Split(RwTxt(RwCnt), Seprator, -1, vbBinaryCompare) Dim ClmCnt As Long If Not UBound(Clms()) = -1 Then ' This might be the case fo extra rows in the text file with no seperators in s For ClmCnt = 1 To HedClmsCnt Let arrOut(RwCnt + 1, ClmCnt) = Clms(ClmCnt - 1) Next ClmCnt Else End If Next RwCnt Rem 2d) Put values from text file into first worksheet Ws1.Range("A1").Resize(UBound(arrOut(), 1), UBound(arrOut(), 2)) = arrOut() End Sub
Try number 12976436. Education in Text files
In support of this Thread: http://www.eileenslounge.com/viewtopic.php?f=30&t=34629
DF.txt
Text file, DF.txt https://app.box.com/s/gw941dh9v8sqhvzin3lo9rfc67fjsbic
Original uploaded DF.txt looked like this as seen for example using a Text Editor. ( Notepad is just one of many available text editors )
10,18052020,9.23,0015378
20,1018GS2026,GS,IN0020010081,0.00,0.00,10.00,0.00 ,0.00,10.00
20,1025GS2021,GS,IN0020010040,0.00,0.00 ……..etc.
You are using a comma in DF.txt to separate the values. Because you are using a comma to separate your values , we sometimes call such a file a comma separated values file., and we often give a comma separated values text file the extension .csv. But you don’t have to. It’s is your choice. Both DF.txt or DF.csv is OK. You can use either for your text file.
You have used DF.txt for your comma separated values text file. That is a bit unusual, but it is OK. Its your choice.
This macro will allow us to examine that text file, ( for simplicity I am using a test file example of just 3 rows )here is the full single string of the text file, shown in two forms:Code:Sub WotsInDF_Text() ' ' http://www.eileenslounge.com/viewtopic.php?p=268809#p268809 What is in DF.txt : https://app.box.com/s/gw941dh9v8sqhvzin3lo9rfc67fjsbic https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13273&viewfull=1#post13273 Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function Dim PathAndFileName As String, TotalFile As String Let PathAndFileName = ThisWorkbook.Path & "\csv Text file Chaos\" & "DF_first 3 rows.txt" ' From vixer zyxw1234 : http://www.eileenslounge.com/viewtopic.php?f=30&t=34629 DF.txt https://app.box.com/s/gw941dh9v8sqhvzin3lo9rfc67fjsbic Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundemental type data input... TotalFile = Space(LOF(FileNum)) '....and wot recives it has to be a string of exactly the right length Get #FileNum, , TotalFile Close #FileNum ' What the fuck is in this string? Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(TotalFile) ' https://excelfox.com/forum/showthread.php/2302-quot-What%e2%80%99s-in-a-String-quot-VBA-break-down-Loop-through-character-contents-of-a-string?p=11016&viewfull=1#post11016 https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11818&viewfull=1#post11818 End Sub
_ as seen in a text editor
_ in a VBA code line form
here the same again, just shown slightly differently for easy of explanationCode:10,18052020,9.23,001537820,1018GS2026,GS,IN0020010081,0.00,0.00,10.00,0.00,0.00,10.0020,1025GS2021,GS,IN0020010040,0.00,0.00,10.00,0.00,0.00,10.00 "10" & Chr(44) & "18052020" & Chr(44) & "9" & "." & "23" & Chr(44) & "0015378" & vbCr & vbLf & "20" & Chr(44) & "1018GS2026" & Chr(44) & "GS" & Chr(44) & "IN0020010081" & Chr(44) & "0" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "10" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "10" & "." & "00" & vbCr & vbLf & "20" & Chr(44) & "1025GS2021" & Chr(44) & "GS" & Chr(44) & "IN0020010040" & Chr(44) & "0" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "10" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "10" & "." & "00"
we see the value seperator comma , ( character 44 ) and the line seperator, vbCr & vbLfCode:"10" & Chr(44) & "18052020" & Chr(44) & "9" & "." & "23" & Chr(44) & "0015378" & vbCr & vbLf & "20" & Chr(44) & "1018GS2026" & Chr(44) & "GS" & Chr(44) & "IN0020010081" & Chr(44) & "0" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "10" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "10" & "." & "00" & vbCr & vbLf & "20" & Chr(44) & "1025GS2021" & Chr(44) & "GS" & Chr(44) & "IN0020010040" & Chr(44) & "0" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "10" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "0" & "." & "00" & Chr(44) & "10" & "." & "00"
In support of this Thread: https://excelfox.com/forum/showthrea...3427#post13427
Alert 24 Mai..csv Alert 24 MaiDotDotcsv.jpg : https://imgur.com/0HsAOLj
We analyse using the same macro as above, with this changed code line
Here is the resultsCode:Let PathAndFileName = ThisWorkbook.Path & "\csv Text file Chaos\" & "Alert 24 Mai..csv" ' https://excelfox.com/forum/showthread.php/2500-Conditionally-delete-entire-row-with-calculation-within-files Share ‘Alert 24 Mai..csv’ : https://app.box.com/s/599q2it3uck3hfwm5kscmmgtn0be66wt
Code:NSE,236,6,>,431555,A,,,,,GTTNSE,25,6,>,431555,A,,,,,GTTNSE,15083,6,>,431555,A,,,,,GTTNSE,17388,6,>,431555,A,,,,,GTTNSE,100,6,>,431555,A,,,,,GTTNSE,22,6,>,431555,A,,,,,GTT,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Entire row of row 3 & row 4 both will be deleted after runing the macro,,,,,,Here again adjusted for clarityCode:"NSE" & Chr(44) & "236" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf & "NSE" & Chr(44) & "25" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf & "NSE" & Chr(44) & "15083" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf & "NSE" & Chr(44) & "17388" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf & "NSE" & Chr(44) & "100" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf & "NSE" & Chr(44) & "22" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vb Lf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "Entire" & " " & "row" & " " & "of" & " " & "row" & " " & "3" & " " & "&" & " " & "row" & " " & "4" & " " & "both" & " " & "will" & " " & "be" & " " & "deleted" & " " & " after" & " " & "runing" & " " & "the" & " " & "macro" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf
.csv text file is using commas , for the value separator, and for the line separate it has the typical convention of vbCr & vbLfCode:"NSE" & Chr(44) & "236" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf & "NSE" & Chr(44) & "25" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf & "NSE" & Chr(44) & "15083" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf & "NSE" & Chr(44) & "17388" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf & "NSE" & Chr(44) & "100" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf & "NSE" & Chr(44) & "22" & Chr(44) & "6" & Chr(44) & Chr(62) & Chr(44) & "431555" & Chr(44) & "A" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "GTT" & vbCr & vbLf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & "Entire" & " " & "row" & " " & "of" & " " & "row" & " " & "3" & " " & "&" & " " & "row" & " " & "4" & " " & "both" & " " & "will" & " " & "be" & " " & "deleted" & " " & "after" & " " & "runing" & " " & "the" & " " & "macro" & Chr(44) & Chr(44) & Chr(44) & Chr(44) & Chr(44) & vbCr & vbLf
In Support of this forum question
https://eileenslounge.com/viewtopic....268481#p268481
_____ Workbook: Converting formulas to valuesC.xlsm ( Using Excel 2007 32 bit )
Worksheet: data
Row\Col Got missing number in column E CSE equivalent CSE equivalent eileenslounge Got one or more missing numbers eileenslounge1 Got missing number in column E eileenslounge2 Others eileenslounge eileenslounge1 eileenslounge2 Others
_____ Workbook: Converting formulas to valuesC.xlsm ( Using Excel 2007 32 bit )
Worksheet: data
Row\Col =IF(F7="","Got one or more missing numbers",IF(E8="","Got missing number in column E","")) CSE equivalent CSE equivalent eileenslounge =IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E","")) eileenslounge1 =IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E","")) eileenslounge2 =IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E","")) Others =IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E","")) eileenslounge =IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E","")) eileenslounge1 =IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E","")) eileenslounge2 =IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E","")) Others =IF(F8:F16="","Got one or more missing numbers",IF(E8:E15="","Got missing number in column E",""))
In Support of this forum question
https://eileenslounge.com/viewtopic....268481#p268481
_____ Workbook: Converting formulas to valuesC.xlsm ( Using Excel 2007 32 bit )
Worksheet: data
Row\Col CSE equivalent CSE equivalent =IF(E7="","Got one or more missing numbers",IF(F8="","Got missing number in column F","")) eileenslounge =IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E","")) eileenslounge1 =IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E","")) eileenslounge2 =IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E","")) Others =IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E","")) eileenslounge =IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E","")) eileenslounge1 =IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E","")) eileenslounge2 =IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E","")) Others =IF(E8:E16="","Got one or more missing numbers",IF(F8:F15="","Got missing number in column E",""))
_____ Workbook: Converting formulas to valuesC.xlsm ( Using Excel 2007 32 bit )
Worksheet: data
Row\Col CSE equivalent CSE equivalent Got missing number in column F eileenslounge Got one or more missing numbers eileenslounge1 Got one or more missing numbers eileenslounge2 Others eileenslounge eileenslounge1 eileenslounge2 Others
Macro accomnpanying last post
Running the above macro on the test data in uploade file will give these results:Code:Sub EvaluateRangeFormulasC() ' https://eileenslounge.com/viewtopic.php?p=268537#p268537 Dim Ws As Worksheet, Rng As Range, Clm As Range, lRow As Long Const fRow As Long = 6: Const sRow As Long = 8 Set Ws = ThisWorkbook.Worksheets("data") ' Let lRow = Ws.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Let lRow = Ws.Range("G" & Ws.Rows.Count & "").End(xlUp).Row ' ' http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11466&viewfull=1#post11466 Making Lr dynamic ( using rng.End(XlUp) for a single column. ) On Error Resume Next Set Rng = Ws.Rows(fRow).SpecialCells(xlCellTypeFormulas) On Error GoTo 0 If Rng Is Nothing Then MsgBox "No formulas!": Exit Sub Let Application.ScreenUpdating = False For Each Clm In Rng Dim strEval As String ' ' Formula in column H Formula in column J Let strEval = Clm.Formula: Debug.Print strEval ' =IF(G6="eileenslounge",1000,F7*E8) =IF(E7="","Got one or more missing numbers",IF(F8="","Got missing number in column F","")) ' modifications to make first formula work in CSE / Range Evaluate sort of a way Let strEval = Replace(strEval, "G6", "G8:G" & lRow & ""): Debug.Print strEval ' =IF(G8:G15="eileenslounge",1000,F7*E8) =IF(E7="","Got one or more missing numbers",IF(F8="","Got missing number in column F","")) Let strEval = Replace(strEval, "F7*E8", "F9:F16*E10:E17" & lRow & ""): Debug.Print strEval ' =IF(G8:G15="eileenslounge",1000,F9:F16*E10:E1715) =IF(E7="","Got one or more missing numbers",IF(F8="","Got missing number in column F","")) Debug.Print ' just to make an emty line in the Immediate window ' modifications required for second formula work in CSE / Range Evaluate sort of a way Let strEval = Replace(strEval, "E7", "E8:E15" & lRow & ""): Debug.Print strEval ' =IF(G8:G15="eileenslounge",1000,F9:F16*E10:E1715) =IF(E8:E1515="","Got one or more missing numbers",IF(F8="","Got missing number in column F","")) Let strEval = Replace(strEval, "F8", "F8:F15" & lRow & ""): Debug.Print strEval ' =IF(G8:G15="eileenslounge",1000,F9:F16*E10:E1715) =IF(E8:E1515="","Got one or more missing numbers",IF(F8:F1515="","Got missing number in column F","")) Let Clm.Offset(sRow - fRow).Resize(lRow - sRow + 1).Value = Evaluate(strEval) Debug.Print ' just to make an emty line in the Immediate window Next Clm Let Application.ScreenUpdating = True End Sub
_____ Workbook: Converting formulas to valuesC.xlsm ( Using Excel 2007 32 bit )
Worksheet: data
Row\Col CSE equivalent CSE equivalent Got missing number in column F eileenslounge Got one or more missing numbers Got one or more missing numbers eileenslounge1 Got one or more missing numbers Got one or more missing numbers eileenslounge2 Others eileenslounge eileenslounge1 eileenslounge2 Others
When in the VB Editor, after running the macro, you can hit keys Ctrl+g to see the following in the Immediate window. It shows the build up of the formulas in a full run
Code:=IF(G6="eileenslounge",1000,F7*E8) =IF(G8:G15="eileenslounge",1000,F7*E8) =IF(G8:G15="eileenslounge",1000,F9:F16*E10:E1715) =IF(G8:G15="eileenslounge",1000,F9:F16*E10:E1715) =IF(G8:G15="eileenslounge",1000,F9:F16*E10:E1715) =IF(E7="","Got one or more missing numbers",IF(F8="","Got missing number in column F","")) =IF(E7="","Got one or more missing numbers",IF(F8="","Got missing number in column F","")) =IF(E7="","Got one or more missing numbers",IF(F8="","Got missing number in column F","")) =IF(E8:E1515="","Got one or more missing numbers",IF(F8="","Got missing number in column F","")) =IF(E8:E1515="","Got one or more missing numbers",IF(F8:F1515="","Got missing number in column F",""))
Some notes related to these posts
https://excelfox.com/forum/showthrea...ll=1#post13318
http://www.eileenslounge.com/viewtopic.php?f=30&t=34610
http://www.eileenslounge.com/viewtop...267706#p267706
.csv file before
http://www.eileenslounge.com/viewtopic.php?f=30&t=34497
After downloading the
ALERT.xlsx
file at that post , I navigsted to it using Windows file explorer and physically changed it in the explorer window without opening it to
Alert29Apr..csv
Double clicking that gives this
_____ Workbook: Alert29Apr..csv ( Using Excel 2007 32 bit )
Worksheet: ALERT
Row\Col
The string of thet file has 9096 Characters!! : https://pastebin.com/Ptk0f7S8
Share ‘9096Characters29Apr.xls’ : https://app.box.com/s/8g72lokzoil9fe6j645xcg8hej82gcn7
This is how it opens in Notepads
9096Characters29AprTextNotepads.JPG : https://imgur.com/USuCebF
Attachment 2928
One of the few things I can see of any sense is towards the start is a "[Content_Types].xml" : -
"Content" & "_" & "Types" & Chr(93) & "." & "xml"
[Content_Types].xml
_____ Workbook: 9096Characters29Apr.xls ( Using Excel 2007 32 bit )
30 2 31 [ 91 32 C 67 33 o 111 34 n 110 35 t 116 36 e 101 37 n 110 38 t 116 39 _ 95 40 T 84 41 y 121 42 p 112 43 e 101 44 s 115 45 ] 93 46 . 46 47 x 120 48 m 109 49 l 108 50 32
This sort of macro gets the long file string.
Code:Sub WhatStringIsInAlertDotCSV() ' http://www.eileenslounge.com/viewtopic.php?f=30&t=34497 Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function Dim PathAndFileName As String, TotalFile As String Let PathAndFileName = ThisWorkbook.Path & "\At Eileen\" & "Alert29Apr..csv" ' This would be made if not existing and we would have a zero lenf string Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundemental type data input... Dim Lenf As Long: Let Lenf = LOF(FileNum) TotalFile = Space(Lenf) '....and wot recives it hs to be a string of exactly the right length Get #FileNum, , TotalFile Close #FileNum ' What the fuck is in this string? Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(TotalFile) ' ' https://excelfox.com/forum/showthread.php/2302-quot-What%E2%80%99s-in-a-String-quot-VBA-break-down-Loop-through-character-contents-of-a-string?p=11015&viewfull=1#post11015 End Sub
There are no issues with the file format changing or in not getting the required format if this file is opened saved closed etc.. manually or using the below macro.
Further we see that we can change things, and even add worksheets, save and reopen... All changes and any added worksheets are still there!!
We are beginig to see the problem, or rather another twist in the confusion that is Avinash
We are beginig to see the problem, or rather another twist in the confusion that is Avinash. We do not always have a .csv file!!!!! - I can see this for example if I manually try to open the file that typically "works" for Avinash Trying to open Alert when it is not a csv.JPG : https://imgur.com/sS2vnw0Attachment 2927Code:Sub OpenEileensAlertDotCSV() ' https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13343&viewfull=1#post13343 Dim PathAndFileName As String ' The following file was uploaded as ALERT.xlsx I dowloaded it and I navigsted to it using Windows file explorer and physically changed it in the explorer window without opening it to Alert29Apr..csv Let PathAndFileName = ThisWorkbook.Path & "\At Eileen\" & "Alert29Apr..csv" Dim Wb As Workbook, WbSaveSimp As Workbook, WbSaveComp As Workbook ' Ws1 As Worksheet Set Wb = Workbooks.Open(PathAndFileName) Set WbSaveSimp = Wb: Set WbSaveComp = Wb Wb.Close: Set Wb = Workbooks.Open(PathAndFileName) Call WhatStringIsInAlertDotCSV Wb.Save: Wb.Close Call WhatStringIsInAlertDotCSV ' ' No issues so far End Sub
( Note: This warning does not appear when opening the file by a macro, such as in the macro above! )
If I try to do a simple Save on such a file either manually or with coding as in the above macro , then ir is done OK. If I attempt a SaveAs then it will want to save it as an Excel File: Wants to SaveAs xlsx file.JPG : https://imgur.com/RAH3E9T Attachment 2929
Furthermore , there is not an issue if I SaveAs manually with a Filename of "Alert29Apr..csv" ,
Save Alert with doubledot csv as xlsx Excel File.JPG
But , it will end up as a new file "Alert29Apr..csv.xlsx
There are not issues with SaveAs saving it with coding: These will give us our Excel file masquerading as a .csv file
Wb.SaveAs Filename:=ThisWorkbook.Path & "\At Eileen" & "Alert29Apr..csv"
Wb.SaveAs Filename:=ThisWorkbook.Path & "\At Eileen" & "Alert29AprRemove a dot.csv"
There are no issues in reopening these files in coding, and also manually if the warning, ( about the file not being the type of the extension ) is ignored
Some notes related to these posts
https://excelfox.com/forum/showthrea...ll=1#post13318
http://www.eileenslounge.com/viewtopic.php?f=30&t=34610
http://www.eileenslounge.com/viewtop...267706#p267706
Later
Posting Permissions
Reply With Quote
Bookmarks