Some extra stuff for this post
https://eileenslounge.com/viewtopic.php?f=27&t=38243
later
Printable View
Some extra stuff for this post
https://eileenslounge.com/viewtopic.php?f=27&t=38243
later
In support of this main forum Thread
https://excelfox.com/forum/showthrea...nto-excel-cell
_1) create new number and place in cell B1 according to last serial number in csv file,
Before
ExcelFile:
_____ Workbook: SerialNumbers.xls ( Using Excel 2007 32 bit )
Worksheet: Sheet1
Row\Col Serial# : TTR0000001 TTR0000002 TTR0000003
Text File:
Code:TTR0000001
TTR0000002
TTR0000003
Run macro:
Code:Sub NewSN()
' Rem 1 Get the text file as a long single 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 & Application.PathSeparator & "serial_number.csv" '
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input...
' Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length
'Get #FileNum, , TotalFile
' Or http://www.eileenslounge.com/viewtopic.php?p=295782&sid=f6dcab07c4d24e00e697fe4343dc7392#p295782
Let TotalFile = Input(LOF(FileNum), FileNum)
Close #FileNum
Rem 2 determine a new number
'2a Current number
If Right(TotalFile, 2) = vbCr & vbLf Then Let TotalFile = Left(TotalFile, Len(TotalFile) - 2) ' Take off last line feed
Dim PosLstLineFeed As Long: Let PosLstLineFeed = InStrRev(TotalFile, vbCr & vbLf, -1, vbBinaryCompare)
Dim CrntNmbr As String: Let CrntNmbr = Mid(TotalFile, PosLstLineFeed + 2)
Let CrntNmbr = Replace(CrntNmbr, "TTR", "", 1, -1, vbBinaryCompare)
'2b creat new number
Let CrntNmbr = CrntNmbr + 1
Let CrntNmbr = Format(CrntNmbr, "0000000")
Let CrntNmbr = "TTR" & CrntNmbr
Rem 3 Put new number in Excel file
Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets.Item(1)
Dim LrB As Long: Let LrB = Ws1.Range("B" & Ws1.Rows.Count & "").End(xlUp).Row
Let Ws1.Range("B" & LrB + 1 & "").Value = CrntNmbr
End Sub
After:
_____ Workbook: SerialNumbers.xls ( Using Excel 2007 32 bit )
Worksheet: Sheet1
Row\Col Serial# : TTR0000001 TTR0000002 TTR0000003 TTR0000004
In support of this main forum Thread
https://excelfox.com/forum/showthrea...nto-excel-cell
_2) save new serial number to csv file below last numbers.
Before :
text File
Excel FileCode:TTR0000001
TTR0000002
TTR0000003
_____ Workbook: SerialNumbers.xls ( Using Excel 2007 32 bit )
Worksheet: Sheet1
Row\Col Serial# : TTR0000001 TTR0000002 TTR0000003 TTR0000004
Run this macro
Code:' https://excelfox.com/forum/showthread.php/2797-find-last-alphanumeric-row-of-txt-file-and-fill-into-excel-cell
Sub SaveLatestSNinTextFile()
Rem 1 Get the text file as a long single 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 & Application.PathSeparator & "serial_number.csv" '
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input...
' Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length
'Get #FileNum, , TotalFile
' Or http://www.eileenslounge.com/viewtopic.php?p=295782&sid=f6dcab07c4d24e00e697fe4343dc7392#p295782
Let TotalFile = Input(LOF(FileNum), FileNum)
Close #FileNum
Rem 2 get latest serial nimber from Excel file
Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets.Item(1)
Dim LrB As Long: Let LrB = Ws1.Range("B" & Ws1.Rows.Count & "").End(xlUp).Row
Dim CrntNmbr As String: Let CrntNmbr = Ws1.Range("B" & LrB & "").Value
Rem 3 add latest serial number to text file
' 3a add a new line and the latest serial number to the string of the entire file
If Right(TotalFile, 2) = vbCr & vbLf Then Let TotalFile = Left(TotalFile, Len(TotalFile) - 2)
Let TotalFile = TotalFile & vbCr & vbLf & CrntNmbr '
' 3b replace the text file with the new string
Dim FileNum2 As Long: Let FileNum2 = FreeFile(0) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Open PathAndFileName For Output As #FileNum2
Print #FileNum2, TotalFile ' write out entire text file
Close #FileNum2
End Sub
After:
Code:TTR0000001
TTR0000002
TTR0000003
TTR0000004
In support of this main forum Thread post:
https://excelfox.com/forum/showthrea...ll=1#post16695
content in one line input reduced sample.txt https://app.box.com/s/grrxh1rl372pzp2exn6em00ovhj4qhih
content in one line output reduced sample.txt https://app.box.com/s/mpyvgf4kj9q04szjtj0255cns24lbxos
https://i.postimg.cc/yYdStNqH/Before...ced-sample.jpg
What’s those two text files got in them?:
Using the Function , Sub WtchaGot_Unic_NotMuchIfYaChoppedItOff(ByVal strIn As String, Optional ByVal FlNme As String) '
, from here: https://excelfox.com/forum/showthrea...ll=1#post15524
https://pastebin.com/eutzzxHv
, and using this Calling macro
Here is the results, for example from the immediate window :Code:' https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA?p=16696&viewfull=1#post16696
Sub SantaComing()
' Rem 1 Get the text file as a long single 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 & Application.PathSeparator & "content in one line input reduced sample.txt" '
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input...
' Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length
'Get #FileNum, , TotalFile
' Or http://www.eileenslounge.com/viewtopic.php?p=295782&sid=f6dcab07c4d24e00e697fe4343dc7392#p295782
Let TotalFile = Input(LOF(FileNum), FileNum)
Close #FileNum
Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(TotalFile)
End Sub
Here are the corresponding results if I do the same to look at the after file ( content in one line output reduced sample.txt https://app.box.com/s/mpyvgf4kj9q04szjtj0255cns24lbxos )Code:Chr(239) & Chr(187) & Chr(191) & "1111" & "." & " Last year" & "," & " I put together this list of the most iconic poems " & vbCr & vbLf & "in the English language" & ";" & " it" & Chr(226) & ChrW(8364) & ChrW(8482) & "s high time to do the same for short " & vbCr & vbLf & "stories" & "." & " But before we go any further" & "," & " you may be asking" & ":" & " What does " & vbCr & vbLf & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " mean in this context" & "?" & " Can a short story " & vbCr & vbLf & "really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "Well" & "," & " who knows" & "," & " but for our purposes" & "," & " " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " means that " & vbCr & vbLf & "the story has somehow wormed"
So, what do we conclude?:Code:Chr(239) & Chr(187) & Chr(191) & "1111" & "." & " Last year" & "," & " I put together this list of the most iconic poems in the English language" & ";" & " it" & Chr(226) & ChrW(8364) & ChrW(8482) & "s high time to do the same for short stories" & "." & " But before we go any further" & "," & " you may be asking" & ":" & " What does " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " mean in this context" & "?" & " Can a short story really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "Well" & "," & " who knows" & "," & " but for our purposes" & "," & " " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " means that the story has somehow wormed"
_ 1) there are no tab characters
_ 2) It seems that the line separator is the commonly used 2 character pair of a carriage return and a line feed, ( In VBA coding, vbCr & vbLf )
_ 3) It seems like the character string we wish to remove, ( the extra new line forming characters ) are three character:
A space and a carriage return and a line feed. ( In VBA coding _ " " & vbCr & vbLf _ )
( _ 4) I expect the OP, ( susan santa 12345 et al. ) , probably would not want to remove all that, but rather replace it with a single space , so I will do that!)
This initial macro seems to do the required:
Here is the corresponding output from my function for the new text file madeCode:Sub ReplaceInTextFileThreeCharacters__Space_vbCr_vbLf__WithA__Space__() ' https://excelfox.com/forum/showthread.php/2817-Make-all-text-file-content-in-one-line-if-a-space-found
' Rem 1 Get the text file as a long single 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 & Application.PathSeparator & "content in one line input reduced sample.txt" '
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input...
' Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length
'Get #FileNum, , TotalFile
' Or http://www.eileenslounge.com/viewtopic.php?p=295782&sid=f6dcab07c4d24e00e697fe4343dc7392#p295782
Let TotalFile = Input(LOF(FileNum), FileNum)
Close #FileNum
Rem 2 remove " " & vbCr & vbLf
' Let TotalFile = Replace(TotalFile, " " & vbCr & vbLf, "", 1, -1, vbBinaryCompare)
Let TotalFile = Replace(TotalFile, " " & vbCr & vbLf, " ", 1, -1, vbBinaryCompare) ' I expect the OP, ( susan santa 12345 et al. ) , probably would not want to remove all that, but rather replace it with a single space , so I do that!
Rem 3 Make new file
Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "new text file.txt"
Dim FileNum2 As Long: Let FileNum2 = FreeFile(0) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Open PathAndFileName For Output As #FileNum2
Print #FileNum2, TotalFile ' write out entire text file
Close #FileNum2
End Sub
That looks very similar to the output requested. Here is the new text file made:Code:Chr(239) & Chr(187) & Chr(191) & "1111" & "." & " Last year" & "," & " I put together this list of the most iconic poems in the English language" & ";" & " it" & Chr(226) & ChrW(8364) & ChrW(8482) & "s high time to do the same for short stories" & "." & " But before we go any further" & "," & " you may be asking" & ":" & " What does " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " mean in this context" & "?" & " Can a short story really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "Well" & "," & " who knows" & "," & " but for our purposes" & "," & " " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " means that the story has somehow wormed" & vbCr & vbLf
new text file.txt https://app.box.com/s/w2zydwa20lr3jyxuk8z7ddjgpl0xrcvn
SantaClawsIsComing.xls https://app.box.com/s/7ken6nf050xd4xqwhxu3av1yebdb74dr
In support of this main forum Thread post:
https://excelfox.com/forum/showthrea...ll=1#post16695
content in one line input reduced sample.txt https://app.box.com/s/grrxh1rl372pzp2exn6em00ovhj4qhih
content in one line output reduced sample.txt https://app.box.com/s/mpyvgf4kj9q04szjtj0255cns24lbxos
https://i.postimg.cc/yYdStNqH/Before...ced-sample.jpg
What’s those two text files got in them?:
Using the Function , Sub WtchaGot_Unic_NotMuchIfYaChoppedItOff(ByVal strIn As String, Optional ByVal FlNme As String) '
, from here: https://excelfox.com/forum/showthrea...ll=1#post15524
https://pastebin.com/eutzzxHv
, and using this Calling macro
Here is the results, for example from the immediate window :Code:' https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA?p=16696&viewfull=1#post16696
Sub SantaComing()
' Rem 1 Get the text file as a long single 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 & Application.PathSeparator & "content in one line input reduced sample.txt" '
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input...
' Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length
'Get #FileNum, , TotalFile
' Or http://www.eileenslounge.com/viewtopic.php?p=295782&sid=f6dcab07c4d24e00e697fe4343dc7392#p295782
Let TotalFile = Input(LOF(FileNum), FileNum)
Close #FileNum
Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(TotalFile)
End Sub
Here are the corresponding results if I do the same to look at the after file ( content in one line output reduced sample.txt https://app.box.com/s/mpyvgf4kj9q04szjtj0255cns24lbxos )Code:Chr(239) & Chr(187) & Chr(191) & "1111" & "." & " Last year" & "," & " I put together this list of the most iconic poems " & vbCr & vbLf & "in the English language" & ";" & " it" & Chr(226) & ChrW(8364) & ChrW(8482) & "s high time to do the same for short " & vbCr & vbLf & "stories" & "." & " But before we go any further" & "," & " you may be asking" & ":" & " What does " & vbCr & vbLf & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " mean in this context" & "?" & " Can a short story " & vbCr & vbLf & "really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "Well" & "," & " who knows" & "," & " but for our purposes" & "," & " " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " means that " & vbCr & vbLf & "the story has somehow wormed"
So, what do we conclude?:Code:Chr(239) & Chr(187) & Chr(191) & "1111" & "." & " Last year" & "," & " I put together this list of the most iconic poems in the English language" & ";" & " it" & Chr(226) & ChrW(8364) & ChrW(8482) & "s high time to do the same for short stories" & "." & " But before we go any further" & "," & " you may be asking" & ":" & " What does " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " mean in this context" & "?" & " Can a short story really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "Well" & "," & " who knows" & "," & " but for our purposes" & "," & " " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " means that the story has somehow wormed"
_ 1) there are no tab characters
_ 2) It seems that the line separator is the commonly used 2 character pair of a carriage return and a line feed, ( In VBA coding, vbCr & vbLf )
_ 3) It seems like the character string we wish to remove, ( the extra new line forming characters ) are three character:
A space and a carriage return and a line feed. ( In VBA coding _ " " & vbCr & vbLf _ )
( _ 4) I expect the OP, ( susan santa 12345 et al. ) , probably would not want to remove all that, but rather replace it with a single space , so I will do that!)
This initial macro seems to do the required:
Here is the corresponding output from my function for the new text file madeCode:Sub ReplaceInTextFileThreeCharacters__Space_vbCr_vbLf__WithA__Space__() ' https://excelfox.com/forum/showthread.php/2817-Make-all-text-file-content-in-one-line-if-a-space-found
' Rem 1 Get the text file as a long single 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 & Application.PathSeparator & "content in one line input reduced sample.txt" '
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input...
' Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length
'Get #FileNum, , TotalFile
' Or http://www.eileenslounge.com/viewtopic.php?p=295782&sid=f6dcab07c4d24e00e697fe4343dc7392#p295782
Let TotalFile = Input(LOF(FileNum), FileNum)
Close #FileNum
Rem 2 remove " " & vbCr & vbLf
' Let TotalFile = Replace(TotalFile, " " & vbCr & vbLf, "", 1, -1, vbBinaryCompare)
Let TotalFile = Replace(TotalFile, " " & vbCr & vbLf, " ", 1, -1, vbBinaryCompare) ' I expect the OP, ( susan santa 12345 et al. ) , probably would not want to remove all that, but rather replace it with a single space , so I do that!
Rem 3 Make new file
Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "new text file.txt"
Dim FileNum2 As Long: Let FileNum2 = FreeFile(0) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Open PathAndFileName For Output As #FileNum2
Print #FileNum2, TotalFile ' write out entire text file
Close #FileNum2
End Sub
That looks very similar to the output requested. Here is the new text file made:Code:Chr(239) & Chr(187) & Chr(191) & "1111" & "." & " Last year" & "," & " I put together this list of the most iconic poems in the English language" & ";" & " it" & Chr(226) & ChrW(8364) & ChrW(8482) & "s high time to do the same for short stories" & "." & " But before we go any further" & "," & " you may be asking" & ":" & " What does " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " mean in this context" & "?" & " Can a short story really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "really be iconic in the way of a poem" & "," & " or a painting" & "," & " or Elvis" & "?" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "Well" & "," & " who knows" & "," & " but for our purposes" & "," & " " & Chr(226) & ChrW(8364) & ChrW(339) & "iconic" & Chr(226) & ChrW(8364) & Chr(157) & " means that the story has somehow wormed" & vbCr & vbLf
new text file.txt https://app.box.com/s/w2zydwa20lr3jyxuk8z7ddjgpl0xrcvn
SantaClawsIsComing.xls https://app.box.com/s/7ken6nf050xd4xqwhxu3av1yebdb74dr
Some notes in support of this forum main post
https://excelfox.com/forum/showthrea...ll=1#post16712
An erroring formula: https://i.postimg.cc/nzbWtGTQ/An-erroring-formula.jpg
https://i.postimg.cc/mcjjYMvb/An-erroring-formula.jpg
Highlight a section, such as the Match section https://i.postimg.cc/5NxZ0pJB/Highlight-match.jpg
https://i.postimg.cc/K4H9pPRj/Highlight-match.jpg
Hit key F9 https://i.postimg.cc/7P1FwdFP/Hit-ke...esult-is-5.jpg
https://i.postimg.cc/Thw7c72B/Hit-ke...esult-is-5.jpg
This reveals that we have 5 , or in total , 5 + 1 = 6
So in the VLookUp we are looking at column 6 in the range given by bk
The names manager tells us which range we want:
Second formula section is the range bk https://i.postimg.cc/JhBFg23L/range-bk.jpg
https://i.postimg.cc/zL8pHpPt/range-bk.jpg
Similarly we can investigate the first section in the erroring formula
Highlight first formula section https://i.postimg.cc/902syY7j/Highli...la-section.jpg
Hit key F9 https://i.postimg.cc/wvLStG3V/Hiut-F...st-section.jpg
This reveals that the Look up value, the value that you are looking for is "DMG1"
Further investigations by trial and error , reveals that some character combinations in cell A2 cause the error. But I do not know why yet ?
Examples:
Not work:
https://i.postimg.cc/YqbTmg2J/d-not-work.jpg
https://i.postimg.cc/d0zbVYQ3/d-MG1-not-work.jpg
Works
https://i.postimg.cc/3xq6tTv1/MG1-works.jpg
In fact, it seems that some character combinations are not allowed as the Look Up value generally , for example try another cell, and I can find a character combination that does not work
https://i.postimg.cc/SK204shH/Not-wo...ters-in-A6.jpg
https://i.postimg.cc/Ff4Cgh1x/Not-wo...ters-in-A6.jpg
I am puzzled.
In fact if you look in detail at the results you are getting when it does appear to work, then they are not alwaysthe correct values.
https://postimg.cc/kBnt3Zzg
https://i.postimg.cc/kBnt3Zzg/Wrong-...m-VLook-Up.jpg https://i.postimg.cc/MKB0RJYV/Wrong-...m-VLook-Up.jpg
Very strange. I am even more puzzled
?????
Update Answer from Sandy https://excelfox.com/forum/showthrea...ll=1#post16717
https://i.postimg.cc/ncHVbyD8/ThatsIt.gifQuote:
Originally Posted by sandy666
https://i.postimg.cc/15VpN7Hj/ThatsIt.jpg https://i.postimg.cc/N5Yy34Z6/ThatsIt.jpg
( I thought I had checked that, but missed something somewhere, I don’t know why I missed that, maybe I think also I need to learn how to use VLoopUp properly! )
Some notes in support of this forum main post
https://excelfox.com/forum/showthrea...ll=1#post16712
An erroring formula: https://i.postimg.cc/nzbWtGTQ/An-erroring-formula.jpg
https://i.postimg.cc/mcjjYMvb/An-erroring-formula.jpg
Highlight a section, such as the Match section https://i.postimg.cc/5NxZ0pJB/Highlight-match.jpg
https://i.postimg.cc/K4H9pPRj/Highlight-match.jpg
Hit key F9 https://i.postimg.cc/7P1FwdFP/Hit-ke...esult-is-5.jpg
https://i.postimg.cc/Thw7c72B/Hit-ke...esult-is-5.jpg
This reveals that we have 5 , or in total , 5 + 1 = 6
So in the VLookUp we are looking at column 6 in the range given by bk
The names manager tells us which range we want:
Second formula section is the range bk https://i.postimg.cc/JhBFg23L/range-bk.jpg
https://i.postimg.cc/zL8pHpPt/range-bk.jpg
Similarly we can investigate the first section in the erroring formula
Highlight first formula section https://i.postimg.cc/902syY7j/Highli...la-section.jpg
Hit key F9 https://i.postimg.cc/wvLStG3V/Hiut-F...st-section.jpg
This reveals that the Look up value, the value that you are looking for is "DMG1"
Further investigations by trial and error , reveals that some character combinations in cell A2 cause the error. But I do not know why yet ?
Examples:
Not work:
https://i.postimg.cc/YqbTmg2J/d-not-work.jpg
https://i.postimg.cc/d0zbVYQ3/d-MG1-not-work.jpg
Works
https://i.postimg.cc/3xq6tTv1/MG1-works.jpg
In fact, it seems that some character combinations are not allowed as the Look Up value generally , for example try another cell, and I can find a character combination that does not work
https://i.postimg.cc/SK204shH/Not-wo...ters-in-A6.jpg
https://i.postimg.cc/Ff4Cgh1x/Not-wo...ters-in-A6.jpg
I am puzzled.
In fact if you look in detail at the results you are getting when it does appear to work, then they are not alwaysthe correct values.
https://postimg.cc/kBnt3Zzg
https://i.postimg.cc/kBnt3Zzg/Wrong-...m-VLook-Up.jpg https://i.postimg.cc/MKB0RJYV/Wrong-...m-VLook-Up.jpg
Very strange. I am even more puzzled
?????
Update Answer from Sandy https://excelfox.com/forum/showthrea...ll=1#post16717
https://i.postimg.cc/ncHVbyD8/ThatsIt.gifQuote:
Originally Posted by sandy666
https://i.postimg.cc/15VpN7Hj/ThatsIt.jpg https://i.postimg.cc/N5Yy34Z6/ThatsIt.jpg
( I thought I had checked that, but missed something somewhere, I don’t know why I missed that, maybe I think also I need to learn how to use VLoopUp properly! )
This is post https://excelfox.com/forum/showthrea...6727#post16727
#post16727
In support of this forum Thread: https://excelfox.com/forum/showthrea...6726#post16726
https://excelfox.com/forum/showthrea...6718#post16718
I want to analyse the text in cells A2, A5, B7, and B10.
I can use my Function , Sub WtchaGot_Unic_NotMuchIfYaChoppedItOff(ByVal strIn As String, Optional ByVal FlNme As String) '
, from here: https://excelfox.com/forum/showthrea...ll=1#post15524
https://pastebin.com/eutzzxHv
In conjunction with this short macro
Here are some of the resultsCode:Option Explicit
' https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA?p=16727&viewfull=1#post16727
' https://excelfox.com/forum/showthread.php/2818-in-VBA-if-the-given-string-is-found-then-delete-everything-between-two-newlines-where-the-string-appears
Sub WotsEeGot()
Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("A2").Value2) ' https://pastebin.com/raw/eutzzxHv
Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("A5").Value2)
Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("B7").Value2)
Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("B10").Value2)
End Sub
So it looks like most important info to glean is that the line separator is as conventional in an Excel cell, vbLfCode:
"in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & vbLf & vbLf & "if the given string is found in any paragraph" & "/" & "line excel cell then delete everything between two newlines where the string appears" & "." & " in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & "searched" & Chr(42) & "string" & vbLf & vbLf & "if the given string is found in any excel cell then delete everything between two newlines where the string appears" & "."
"in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in " & "#" & "VBA" & """" & vbLf & "if the given string is found in any excel cell then delete paragraph" & "/" & "line everything between two newlines where the string appears" & "." & vbLf & "in VBA if the given string is found then delete paragraph" & "/" & "line everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & vbLf & "if the given string is found in any excel cell then delete everything between two newlines where the string appears" & "." & vbLf & "searched" & Chr(42) & "string"
"in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & vbLf & vbLf & "if the given string is found in any paragraph" & "/" & "line excel cell then delete everything between two newlines where the string appears" & "." & " in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & "searched" & Chr(42) & "string" & vbLf & vbLf & "if the given string is found in any excel cell then delete everything between two newlines where the string appears" & "."
"in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in " & "#" & "VBA" & """" & vbLf & "if the given string is found in any excel cell then delete paragraph" & "/" & "line everything between two newlines where the string appears" & "." & vbLf & "in VBA if the given string is found then delete paragraph" & "/" & "line everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & vbLf & "if the given string is found in any excel cell then delete everything between two newlines where the string appears" & "." & vbLf & "searched" & Chr(42) & "string"
This is post https://excelfox.com/forum/showthrea...ll=1#post19610
#post19610
It was copied initially before I edited it from the post above, #post16727 , and that #post16727 stayes yellow highlighted after the copy
Some note in support of this main forum post
https://eileenslounge.com/viewtopic....303644#p303644
„WieGehtsYouTubeServerChrome.txt“ https://app.box.com/s/a7k2izgyzqhd7f98hlaq9csw0l4tyyl6
This is post https://excelfox.com/forum/showthrea...6727#post16727
#post16727
In support of this forum Thread: https://excelfox.com/forum/showthrea...6726#post16726
https://excelfox.com/forum/showthrea...6718#post16718
I want to analyse the text in cells A2, A5, B7, and B10.
I can use my Function , Sub WtchaGot_Unic_NotMuchIfYaChoppedItOff(ByVal strIn As String, Optional ByVal FlNme As String) '
, from here: https://excelfox.com/forum/showthrea...ll=1#post15524
https://pastebin.com/eutzzxHv
In conjunction with this short macro
Here are some of the resultsCode:Option Explicit
' https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA?p=16727&viewfull=1#post16727
' https://excelfox.com/forum/showthread.php/2818-in-VBA-if-the-given-string-is-found-then-delete-everything-between-two-newlines-where-the-string-appears
Sub WotsEeGot()
Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("A2").Value2) ' https://pastebin.com/raw/eutzzxHv
Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("A5").Value2)
Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("B7").Value2)
Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Range("B10").Value2)
End Sub
So it looks like most important info to glean is that the line separator is as conventional in an Excel cell, vbLfCode:
"in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & vbLf & vbLf & "if the given string is found in any paragraph" & "/" & "line excel cell then delete everything between two newlines where the string appears" & "." & " in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & "searched" & Chr(42) & "string" & vbLf & vbLf & "if the given string is found in any excel cell then delete everything between two newlines where the string appears" & "."
"in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in " & "#" & "VBA" & """" & vbLf & "if the given string is found in any excel cell then delete paragraph" & "/" & "line everything between two newlines where the string appears" & "." & vbLf & "in VBA if the given string is found then delete paragraph" & "/" & "line everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & vbLf & "if the given string is found in any excel cell then delete everything between two newlines where the string appears" & "." & vbLf & "searched" & Chr(42) & "string"
"in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & vbLf & vbLf & "if the given string is found in any paragraph" & "/" & "line excel cell then delete everything between two newlines where the string appears" & "." & " in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & "searched" & Chr(42) & "string" & vbLf & vbLf & "if the given string is found in any excel cell then delete everything between two newlines where the string appears" & "."
"in VBA if the given string is found then delete everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in " & "#" & "VBA" & """" & vbLf & "if the given string is found in any excel cell then delete paragraph" & "/" & "line everything between two newlines where the string appears" & "." & vbLf & "in VBA if the given string is found then delete paragraph" & "/" & "line everything between two newlines where the string appears" & "." & vbLf & """" & "Looking for help in VBA" & """" & vbLf & "if the given string is found in any excel cell then delete everything between two newlines where the string appears" & "." & vbLf & "searched" & Chr(42) & "string"
This is post https://excelfox.com/forum/showthrea...ll=1#post19610
#post19610
It was copied initially before I edited it from the post above, #post16727 , and that #post16727 stayes yellow highlighted after the copy
Some note in support of this main forum post
https://eileenslounge.com/viewtopic....303644#p303644
„WieGehtsYouTubeServerChrome.txt“ https://app.box.com/s/a7k2izgyzqhd7f98hlaq9csw0l4tyyl6
Some extra notes for this Thread
https://www.excelfox.com/forum/showt...ll=1#post23215
Some extra notes for this Thread
https://www.excelfox.com/forum/showt...ll=1#post23215
Some extra notes for this Thread
https://www.excelfox.com/forum/showt...ll=1#post23215
Some extra notes for this Thread
https://www.excelfox.com/forum/showt...ll=1#post23215
https://i.postimg.cc/cKzrrVTK/My-Rai...-main-line.jpg
Its beautiful. I do it early while it’s still sunny, so I get a nice Sun tan on my body so that I will be even more beautiful in my YouTube videos...
( macro used in atttched file, FilTit3.xlsm , is also here: https://www.excelfox.com/forum/showt...ll=1#post23212 )
later
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=oVb1RfcSHLM&lc=UgwTq-jZlZLnLQ5VB8Z4AaABAg.9Hroz-OyWog9tYjSMc1qjA
https://www.youtube.com/watch?v=0pbsf6sox34&lc=Ugxp9JFvvejnqA68W1t4AaABAg
https://www.youtube.com/watch?v=kfQC-sQxMcw&lc=UgyCxQWypNIhG2nUn794AaABAg.9q1p6q7ah839t UQl_92mvg
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgyOh-eR43LvlIJLG5p4AaABAg.9isnKJoRfbL9itPC-4uckb
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugy1B1aQnHq2WbbucmR4AaABAg. 9isY3Ezhx4j9itQLuif26T
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgxxajSt03TX1wxh3IJ4AaABAg. 9irSL7x4Moh9itTRqL7dQh
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwJAAPbp8dhkW2X1Uh4AaABAg. 9iraombnLDb9itV80HDpXc
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgzIzQ6MQ5kTpuLbIuB4AaABAg. 9is0FSoF2Wi9itWKEvGSSq
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK75iCEaGN
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK7XF33njy
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCSgpAqA1
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCy--3x8E
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwNaJiNATXshvJ0Zz94AaABAg. 9iEktVkTAHk9iF9_pdshr6
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iFAZq-JEZ-
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgxV2r7KQnuAyZVLHH54AaABAg. 9iDVgy6wzct9iFBxma9zXI
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwnYuSngiuYaUhEMWN4AaABAg. 9iDQN7TORHv9iFGQQ5z_3f
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwJ3yzdk_EE98dndmt4AaABAg. 9iDLC2uEPRW9iFGvgk11nH
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgyDWAVqCa4yMot463x4AaABAg. 9iH3wvUZj3n9iHnpOxOeXa
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwvLFdMEAba5rLHIz94AaABAg. 9iGReNGzP4v9iHoeaCpTG8
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iHpsWCdJ5I
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
I am going jogging in the Sun
:)
I can't take my camera, because I jog fast and very hard, so I might break it. Here is pic from about 18 months ago after I first discovered my old train line. So then I stole some wood and pallets and made a nice secret place to sit.Quote:
Originally Posted by sandy666
https://i.postimg.cc/w16v0JRD/Pooh-B...ay-Station.jpg
I go there sometimes and drink Bier. Not often because always drinking beer is unhealthy. But when I do go I drink lots. I only did it 2 times this summer. Maybe in a few days before Summer end I will go again. Then I go with bike and trailer full with beer. Then I can take a new picture..
mmghg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=316441#p316441
https://eileenslounge.com/viewtopic.php?p=324736#p324736
https://eileenslounge.com/viewtopic.php?p=324990#p324990
https://eileenslounge.com/viewtopic.php?f=27&t=41937&p=325485#p325485
https://eileenslounge.com/viewtopic.php?p=325609#p325609
https://eileenslounge.com/viewtopic.php?p=325610#p325610
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGADdPM65i9PG
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGADdPQHFk_zm
http://www.eileenslounge.com/viewtopic.php?p=324457#p324457
http://www.eileenslounge.com/viewtopic.php?p=324064#p324064
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg. ADd4m2zp_xDADd6Nnotj1C
s://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgySdtXqcaA27wQLd1t4AaABAg
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959
http://www.eileenslounge.com/viewtopic.php?f=30&t=41784
http://www.eileenslounge.com/viewtopic.php?p=323966#p323966
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960
http://www.eileenslounge.com/viewtopic.php?p=323894#p323894
http://www.eileenslounge.com/viewtopic.php?p=323843#p323843
https://www.youtube.com/watch?v=fEHKPhJxgBA&lc=Ugxx8_MjhC9FDaQgcHN4AaABAg
https://www.youtube.com/watch?v=jpjYm4UvyWk&lc=Ugx_Qd4rfAN_ZYcJbo94AaABAg. ACGbG9c76OWACGbjKa7H8k
https://www.youtube.com/watch?v=jpjYm4UvyWk&lc=Ugx_Qd4rfAN_ZYcJbo94AaABAg
https://www.youtube.com/watch?v=GyPHaydeng0&lc=UgzE4a4f_e_y9Rk5OR94AaABAg
https://www.youtube.com/watch?v=I5FkNG94BcQ&lc=UgxXnkEHqulXSR5tXwh4AaABAg
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABa6BSa173Z
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABa6-64Xpgl
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABa5ms39yjd
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABa5ZXJwRCM
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABa4Pr15NUt
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABa4I83JelY
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGADdMo2n-hyF
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
jhkjhh
vmvv
hkjhg
Some Extra notes for this forum post
https://eileenslounge.com/viewtopic....310565#p310565
https://eileenslounge.com/viewtopic....295906#p295906 Macro Correction(.txt to xlsb)
Some Extra notes for this forum post
https://eileenslounge.com/viewtopic....310565#p310565
https://eileenslounge.com/viewtopic....310720#p310720
Here is an interesting use of the "scripting.filesystemobject"
Question: extract only a .csv name file from url string, for example:
ANPR_archivio_comuni.csv
comuni.csv
cap.csv
comuni_codici-catastali.csv
stemmi.csv
, from
https://www.anagrafenazionale.intern...vio_comuni.csv
https://raw.githubusercontent.com/op...ati/comuni.csv
https://raw.githubusercontent.com/op...n/dati/cap.csv
https://raw.githubusercontent.com/op...-catastali.csv
https://raw.githubusercontent.com/op...ati/stemmi.csv
Answer:
The answer here almost works, there is the deliberate mistake of a missing "…. Remember, snb, " In VBA almost every double quote should be followed by a 'closing' quote" … do that and then it works
join(filter(split(replace(createobject("scripting. filesystemobject").opentextfile("G:\OF\nomefilecsv .txt").readall,vbcrlf,"/"),"/"),".csv"),vblf)
Lets look at that
It is a "one liner". Maybe we can use the ideas there to get a whole text file.
Here it is, broken down / opened up, for future reference.
Idea for a "one liner" toCode:' https://eileenslounge.com/viewtopic.php?p=310565#p310565
'Sub M_snb()
' msgbox join(filter(split(replace(createobject("scripting.filesystemobject").opentextfile("G:\OF\nomefilecsv.txt).readall,vbcrlf,"/"),"/"),".csv"),vblf)
'End Sub
Sub M_snb()
Dim vTemp As Variant
Let vTemp = CreateObject("scripting.filesystemobject").opentextfile("" & ThisWorkbook.Path & "\nomefilecsv.txt").readall
'https://www.anagrafenazionale.interno.it/wp-content/uploads/ANPR_archivio_comuni.csv
'https://raw.githubusercontent.com/opendatasicilia/comuni-italiani/main/dati/comuni.csv
'https://raw.githubusercontent.com/opendatasicilia/comuni-italiani/main/dati/cap.csv
'https://raw.githubusercontent.com/opendatasicilia/comuni-italiani/main/dati/comuni_codici-catastali.csv
'https://raw.githubusercontent.com/opendatasicilia/comuni-italiani/main/dati/stemmi.csv
Let vTemp = Replace(vTemp, vbCr & vbLf, "/")
' https://www.anagrafenazionale.interno.it/wp-content/uploads/ANPR_archivio_comuni.csv/https://raw.githubusercontent.com/opendatasicilia/comuni-italiani/main/dati/comuni.csv/https://raw.githubusercontent.com/opendatasicilia/comuni-italiani/main/dati/cap.csv/https://raw.githubusercontent.com/opendatasicilia/comuni-italiani/main/dati/comuni_codici-catastali.csv/https://raw.githubusercontent.com/opendatasicilia/comuni-italiani/main/dati/stemmi.csv
Let vTemp = Split(vTemp, "/") ' https://i.postimg.cc/1V1yf56w/v-Temp...Temp-slash.jpg
' https://i.postimg.cc/gjnjtx2S/v-Temp...Temp-slash.jpg
Let vTemp = Filter(Sourcearray:=vTemp, Match:=".csv", Include:=True, Compare:=vbBinaryCompare) ' https://i.postimg.cc/tn4qWRDc/v-Temp...-Match-csv.jpg
' https://i.postimg.cc/0jpzgbS9/v-Temp...-Match-csv.jpg
Let vTemp = Join(vTemp, vbCr & vbLf)
'ANPR_archivio_comuni.csv
'comuni.csv
'cap.csv
'comuni_codici -catastali.csv
'stemmi.csv
_ bring a text file into an Excel worksheet,
or
_ bring a text file into a VBA variable
The idea, summarised in words is, to do, all in one line …
_The entire text file is brought ( into a string ) using the CreateObject("scripting.filesystemobject").opentex tfile("TextFile") .readall thing
_ the line separator ( most likely the invisible character pair, vbCr & vbLf ) will be replaced by the column separator , the thing which most commonly is the comma ,
_ the Split of the modified string by the , separator , to give a long single array 1 dimensional array,
_ finally the 2Darray = Index ( 1Darray, Rws , Clms() ) ideas , for example here , https://www.excelfox.com/forum/showt...3287#post23287 , will be used to give us the final array or range of values
First try with simple 3 row, 2 column array
a, b
c, d
e, f
https://i.postimg.cc/CBH65d73/3-Row2...-Text-File.jpg
I made that manually. Here is a quick check on what it is made up of:
Using this,
, we get results of this form for its character contents,Code:Sub QuickTxtFileCheck()
Rem 0 Path info
Dim PathAndFileName As String, TotalFile As String
Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "3Row2ColumnTextFile.txt" ' CHANGE TO SUIT From vixer zyxw1234 : http://www.eileenslounge.com/viewtopic.php?f=30&t=34629 DF.txt https://app.box.com/s/gw941dh9v8sqhvzin3lo9rfc67fjsbic
' My usually used way
Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundemental type data input...
Let TotalFile = Space(LOF(FileNum)) '....and wot recives it has to be a string of exactly the right length
Get #FileNum, , TotalFile
Close #FileNum
Debug.Print TotalFile
' a , b
' c , d
' e , f
Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(TotalFile)
' "a" & "," & "b" & vbCr & vbLf & "c" & "," & "d" & vbCr & vbLf & "e" & "," & "f"
' using the CreateObject("scripting.filesystemobject").opentextfile("TextFile") thing
Let TotalFile = CreateObject("scripting.filesystemobject").opentextfile(PathAndFileName).readall
Debug.Print TotalFile
' a , b
' c , d
' e , f
Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(TotalFile)
' "a" & "," & "b" & vbCr & vbLf & "c" & "," & "d" & vbCr & vbLf & "e" & "," & "f"
End Sub
06 Sep 2023
Lenf is 13a,b
c,d
e,f06 Sep 2023
Lenf is 13a,b
c,d
e,f1 a 97 1 a 97 2 , 44 2 , 44 3 b 98 3 b 98 4 13 4 13 5 10 5 10 6 c 99 6 c 99 7 , 44 7 , 44 8 d 100 8 d 100 9 13 9 13 10 10 10 10 11 e 101 11 e 101 12 , 44 12 , 44 13 f 102 13 f 102
, so no surprise, all looks as expected.
Some coding attempts in the next post
FileSystemObject OpenTextFile ReadAll One liner_……. Continued from last post
This basic coding does the job, if we know the dimensions of the text file, for example that the rows are 3 and the columns 2
To make it more flexible, we would need to get the dimensions of the text file from the text file, in the last example that would be to get the column count 2 and row count 3 in the final formulaCode:Sub FileSystemObjectOpenTextFileReadAll() ' https://www.excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA-CSV-stuff?p=23290&viewfull=1#post23290
Dim vTemp As Variant
Let vTemp = CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall ' _The entire text file is brought ( into a string ) using the CreateObject("scripting.filesystemobject").opentextfile("TextFile").readall thing
Let vTemp = Replace(vTemp, vbCr & vbLf, ",", 1, -1, vbBinaryCompare) ' _ the line separator ( most likely the invisible character pair, vbCr & vbLf ) will be replaced by the column separator , the thing which most commonly is the comma ,
Let vTemp = Split(vTemp, ",", -1, vbBinaryCompare) ' _ the split of the modified string by the , separator , to give a long single array 1 dimensional array,
Let vTemp = Application.Index(vTemp, 1, Evaluate("=COLUMN(A:B)+((Row(1:3)-1)*2)")) ' _ finally the 2Darray = Index ( 1Darray, Rws , Clms() ) ideas , for example here , https://www.excelfox.com/forum/showt...3287#post23287 , will be used to give us the final array or range of values
' Or
Let vTemp = Application.Index(Split(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall, vbCr & vbLf, ","), ","), 1, Evaluate("=COLUMN(A:B)+((Row(1:3)-1)*2)"))
' Or
Let vTemp = Application.Index(Split(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall, vbCr & vbLf, ","), ","), 1, Evaluate("=COLUMN(A:" & Split(Cells(1, 2).Address, "$")(1) & ")+((Row(1:3)-1)*2)"))
' ' https://i.postimg.cc/WdbTjR2b/File-S...e-Read-All.jpg
Let Range("A20").Resize(3, 2) = Application.Index(Split(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall, vbCr & vbLf, ","), ","), 1, Evaluate("=COLUMN(A:" & Split(Cells(1, 2).Address, "$")(1) & ")+((Row(1:3)-1)*2)"))
End Sub
………..Evaluate("=COLUMN(A:" & Split(Cells(1, 2).Address, "$")(1) & ")+((Row(1:3)-1)*2)"))
This next code gets you there,
Code:Sub FileSystemObjectOpenTextFileReadAll_() ' https://www.excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA-CSV-stuff?p=23290&viewfull=1#post23290
Dim vTemp As Variant
Let vTemp = Application.Index(Split(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall, vbCr & vbLf, ","), ","), 1, Evaluate("=COLUMN(A:" & Split(Cells(1, 2).Address, "$")(1) & ")+((Row(1:3)-1)*2)"))
Dim RwsCnt As Long, ClmsCnt As Long
Let RwsCnt = 3: ClmsCnt = 2
Let vTemp = Application.Index(Split(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall, vbCr & vbLf, ","), ","), 1, Evaluate("=COLUMN(A:" & Split(Cells(1, ClmsCnt).Address, "$")(1) & ")+((Row(1:" & RwsCnt & ")-1)*" & ClmsCnt & ")"))
' to get the row count, we look at the (difference in length between the main string and the main string less the vbCr & vbLf pairs) / 2 and then add 1 to that since we have 1 less vbCr & vbLf pairs than there are rows
Let vTemp = CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall() ' _The entire text file is brought ( into a string ) using the CreateObject("scripting.filesystemobject").opentextfile("TextFile").readall thing
Let vTemp = (Len(vTemp) - Len(Replace(vTemp, vbCr & vbLf, "", 1, -1, vbBinaryCompare))) / 2 + 1
' or
Let RwsCnt = (Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall, vbCr & vbLf, ""))) / 2 + 1
' to get the column count get the (difference in length in a line and the line without any seperator) + 1 since there willl be one more columns than there are seperators ,
Let vTemp = CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline() '
Let vTemp = (Len(vTemp) - Len(Replace(vTemp, ",", "", 1, -1, vbBinaryCompare))) + 1
' or
Let ClmsCnt = (Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline()) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline(), ",", ""))) + 1
' So
Let vTemp = Application.Index(Split(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall, vbCr & vbLf, ","), ","), 1, _
Evaluate("=COLUMN(A:" & Split(Cells(1, (Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline()) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline(), ",", ""))) + 1).Address, "$")(1) & ")+((Row(1:" & (Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall, vbCr & vbLf, ""))) / 2 + 1 & ")-1)*" & (Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline()) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline(), ",", ""))) + 1 & ")"))
' or
Let Range("A20").Resize(RwsCnt, ClmsCnt) = Application.Index(Split(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall, vbCr & vbLf, ","), ","), 1, _
Evaluate("=COLUMN(A:" & Split(Cells(1, (Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline()) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline(), ",", ""))) + 1).Address, "$")(1) & ")+((Row(1:" & (Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall, vbCr & vbLf, ""))) / 2 + 1 & ")-1)*" & (Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline()) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline(), ",", ""))) + 1 & ")"))
Let Range("A20").Resize((Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall, vbCr & vbLf, ""))) / 2 + 1, (Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline()) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline(), ",", ""))) + 1) = Application.Index(Split(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall, vbCr & vbLf, ","), ","), 1, _
Evaluate("=COLUMN(A:" & Split(Cells(1, (Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline()) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline(), ",", ""))) + 1).Address, "$")(1) & ")+((Row(1:" & (Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readall, vbCr & vbLf, ""))) / 2 + 1 & ")-1)*" & (Len(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline()) - Len(Replace(CreateObject("scripting.filesystemobject").opentextfile(ThisWorkbook.Path & "\3Row2ColumnTextFile.txt").readline(), ",", ""))) + 1 & ")"))
' https://i.postimg.cc/WdbTjR2b/File-S...e-Read-All.jpg
End Sub
even more Later
fsdf
f
sd
f
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=oVb1RfcSHLM&lc=UgwTq-jZlZLnLQ5VB8Z4AaABAg.9Hroz-OyWog9tYjSMc1qjA
https://www.youtube.com/watch?v=0pbsf6sox34&lc=Ugxp9JFvvejnqA68W1t4AaABAg
https://www.youtube.com/watch?v=kfQC-sQxMcw&lc=UgyCxQWypNIhG2nUn794AaABAg.9q1p6q7ah839t UQl_92mvg
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgyOh-eR43LvlIJLG5p4AaABAg.9isnKJoRfbL9itPC-4uckb
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugy1B1aQnHq2WbbucmR4AaABAg. 9isY3Ezhx4j9itQLuif26T
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgxxajSt03TX1wxh3IJ4AaABAg. 9irSL7x4Moh9itTRqL7dQh
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwJAAPbp8dhkW2X1Uh4AaABAg. 9iraombnLDb9itV80HDpXc
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgzIzQ6MQ5kTpuLbIuB4AaABAg. 9is0FSoF2Wi9itWKEvGSSq
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK75iCEaGN
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK7XF33njy
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCSgpAqA1
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCy--3x8E
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwNaJiNATXshvJ0Zz94AaABAg. 9iEktVkTAHk9iF9_pdshr6
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iFAZq-JEZ-
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgxV2r7KQnuAyZVLHH54AaABAg. 9iDVgy6wzct9iFBxma9zXI
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwnYuSngiuYaUhEMWN4AaABAg. 9iDQN7TORHv9iFGQQ5z_3f
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwJ3yzdk_EE98dndmt4AaABAg. 9iDLC2uEPRW9iFGvgk11nH
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgyDWAVqCa4yMot463x4AaABAg. 9iH3wvUZj3n9iHnpOxOeXa
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwvLFdMEAba5rLHIz94AaABAg. 9iGReNGzP4v9iHoeaCpTG8
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iHpsWCdJ5I
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
even more Later
fsdf
f
sd
f
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=oVb1RfcSHLM&lc=UgwTq-jZlZLnLQ5VB8Z4AaABAg.9Hroz-OyWog9tYjSMc1qjA
https://www.youtube.com/watch?v=0pbsf6sox34&lc=Ugxp9JFvvejnqA68W1t4AaABAg
https://www.youtube.com/watch?v=kfQC-sQxMcw&lc=UgyCxQWypNIhG2nUn794AaABAg.9q1p6q7ah839t UQl_92mvg
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgyOh-eR43LvlIJLG5p4AaABAg.9isnKJoRfbL9itPC-4uckb
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugy1B1aQnHq2WbbucmR4AaABAg. 9isY3Ezhx4j9itQLuif26T
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgxxajSt03TX1wxh3IJ4AaABAg. 9irSL7x4Moh9itTRqL7dQh
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwJAAPbp8dhkW2X1Uh4AaABAg. 9iraombnLDb9itV80HDpXc
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgzIzQ6MQ5kTpuLbIuB4AaABAg. 9is0FSoF2Wi9itWKEvGSSq
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK75iCEaGN
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK7XF33njy
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCSgpAqA1
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCy--3x8E
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwNaJiNATXshvJ0Zz94AaABAg. 9iEktVkTAHk9iF9_pdshr6
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iFAZq-JEZ-
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgxV2r7KQnuAyZVLHH54AaABAg. 9iDVgy6wzct9iFBxma9zXI
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwnYuSngiuYaUhEMWN4AaABAg. 9iDQN7TORHv9iFGQQ5z_3f
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwJ3yzdk_EE98dndmt4AaABAg. 9iDLC2uEPRW9iFGvgk11nH
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgyDWAVqCa4yMot463x4AaABAg. 9iH3wvUZj3n9iHnpOxOeXa
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwvLFdMEAba5rLHIz94AaABAg. 9iGReNGzP4v9iHoeaCpTG8
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iHpsWCdJ5I
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
even more Later
fsdf
f
sd
f
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=oVb1RfcSHLM&lc=UgwTq-jZlZLnLQ5VB8Z4AaABAg.9Hroz-OyWog9tYjSMc1qjA
https://www.youtube.com/watch?v=0pbsf6sox34&lc=Ugxp9JFvvejnqA68W1t4AaABAg
https://www.youtube.com/watch?v=kfQC-sQxMcw&lc=UgyCxQWypNIhG2nUn794AaABAg.9q1p6q7ah839t UQl_92mvg
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgyOh-eR43LvlIJLG5p4AaABAg.9isnKJoRfbL9itPC-4uckb
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugy1B1aQnHq2WbbucmR4AaABAg. 9isY3Ezhx4j9itQLuif26T
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgxxajSt03TX1wxh3IJ4AaABAg. 9irSL7x4Moh9itTRqL7dQh
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwJAAPbp8dhkW2X1Uh4AaABAg. 9iraombnLDb9itV80HDpXc
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgzIzQ6MQ5kTpuLbIuB4AaABAg. 9is0FSoF2Wi9itWKEvGSSq
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK75iCEaGN
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK7XF33njy
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCSgpAqA1
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCy--3x8E
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwNaJiNATXshvJ0Zz94AaABAg. 9iEktVkTAHk9iF9_pdshr6
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iFAZq-JEZ-
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgxV2r7KQnuAyZVLHH54AaABAg. 9iDVgy6wzct9iFBxma9zXI
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwnYuSngiuYaUhEMWN4AaABAg. 9iDQN7TORHv9iFGQQ5z_3f
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwJ3yzdk_EE98dndmt4AaABAg. 9iDLC2uEPRW9iFGvgk11nH
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgyDWAVqCa4yMot463x4AaABAg. 9iH3wvUZj3n9iHnpOxOeXa
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwvLFdMEAba5rLHIz94AaABAg. 9iGReNGzP4v9iHoeaCpTG8
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iHpsWCdJ5I
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
This is post 111, Page 12 from this Thread we are in https://www.excelfox.com/forum/showt...-VBA-CSV-stuff
https://www.excelfox.com/forum/showt...ll=1#post23979
https://www.excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA-CSV-stuff?p=23979&viewfull=1#post23979
https://www.excelfox.com/forum/showt...V-stuff/page12
https://www.excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA-CSV-stuff/page12
In support of these forum posts
https://www.excelfox.com/forum/showt...ll=1#post23969
https://eileenslounge.com/viewtopic....313975#p313975
Now here’s a thing….https://eileenslounge.com/viewtopic....313961#p313961Quote:
Originally Posted by SpeakEasy
After a bit of initial looking into this,
https://www.excelfox.com/forum/showt...ll=1#post23971
https://www.excelfox.com/forum/showt...ll=1#post23969
https://eileenslounge.com/viewtopic....313971#p313971
MMPropertyTest https://app.box.com/s/27u7dyjee3rez44pdjq52uu2e7tgzu8v
https://eileenslounge.com/viewtopic....314037#p314037
propkey h.txt https://app.box.com/s/q8klctlcfka8s1uecklbf15n75cxc3v2
, the TLDR is that I got a text file, propkey h.txt , with useful things in it. I want to get a more simpler list of stuff from that. This Thread is the place to do that, :)
propkey h.txt What does it look like
It look nice and well ordered, - do a search for example on a size property, and we see it as part of an already quite well structured list
https://i.postimg.cc/bvbCYCh1/propkey-h.jpg
Attachment 5748 https://i.postimg.cc/WDN6X7t4/propkey-h.jpg
This is that text, as seen on the picture above, in the text file,
With a typical macro like this un, we can take a look at that. (We are basically splitting the text up using something that appears to be used once for every property ,Code:// Name: System.Size -- PKEY_Size
// Type: UInt64 -- VT_UI8
// FormatID: (FMTID_Storage) {B725F130-47EF-101A-A5F1-02608C9EEBAC}, 12 (PID_STG_SIZE)
//
//
DEFINE_PROPERTYKEY(PKEY_Size, 0xB725F130, 0x47EF, 0x101A, 0xA5, 0xF1, 0x02, 0x60, 0x8C, 0x9E, 0xEB, 0xAC, 12);
#define INIT_PKEY_Size { { 0xB725F130, 0x47EF, 0x101A, 0xA5, 0xF1, 0x02, 0x60, 0x8C, 0x9E, 0xEB, 0xAC }, 12 }
// Name: System.
)
That macro and the function , WtchaGot_Unic_NotMuchIfYaChoppedItOff( , in the uploaded fileCode:Option Explicit
' https://www.excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA-CSV-stuff?p=23979&viewfull=1#post23979
Sub ExtendedPropertiesList()
' Rem 1 Get the text file as a long single 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 & Application.PathSeparator & "propkey h.txt" '
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input...
' Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length
'Get #FileNum, , TotalFile
' Or http://www.eileenslounge.com/viewtopic.php?p=295782&sid=f6dcab07c4d24e00e697fe4343dc7392#p295782
Let TotalFile = Input(LOF(FileNum), FileNum)
Close #FileNum
' Rem 2 Split the Prophs
Dim arrProphs() As String: Let arrProphs() = Split(TotalFile, "// Name: System.", -1, vbBinaryCompare)
' 2a) Quick look at list
Dim LCnt As Long: Let LCnt = UBound(arrProphs())
Dim Rws() As Variant, Clms() As Variant, VertList() As Variant
Let Rws() = Evaluate("ROW(1:" & LCnt + 1 & ")/ROW(1:" & LCnt + 1 & ")")
Let Clms() = Evaluate("ROW(1:" & LCnt + 1 & ")")
Let VertList() = Application.Index(arrProphs(), Rws(), Clms())
Let Me.Range("A1:A" & LCnt & "") = VertList()
Me.Cells.WrapText = False
' 2b) Look at some example props using function WtchaGot_Unic_NotMuchIfYaChoppedItOff
' The next text is copied from cell A 350
' "Size -- PKEY_Size
' // Type: UInt64 -- VT_UI8
' // FormatID: (FMTID_Storage) {B725F130-47EF-101A-A5F1-02608C9EEBAC}, 12 (PID_STG_SIZE)
' //
' //
' DEFINE_PROPERTYKEY(PKEY_Size, 0xB725F130, 0x47EF, 0x101A, 0xA5, 0xF1, 0x02, 0x60, 0x8C, 0x9E, 0xEB, 0xAC, 12);
' #define INIT_PKEY_Size { { 0xB725F130, 0x47EF, 0x101A, 0xA5, 0xF1, 0x02, 0x60, 0x8C, 0x9E, 0xEB, 0xAC }, 12 }
'
' "
' The next text is copied from watch window at arrProphs()(349)
' : arrProphs()(349) : "Size -- PKEY_Size
' // Type: UInt64 -- VT_UI8
' // FormatID: (FMTID_Storage) {B725F130-47EF-101A-A5F1-02608C9EEBAC}, 12 (PID_STG_SIZE)
' //
' //
' DEFINE_PROPERTYKEY(PKE"
Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(arrProphs()(349), "Size349")
Stop
End Sub
Some results and discussions in next post
This is post 112, Page 12 from this Thread we are in https://www.excelfox.com/forum/showt...-VBA-CSV-stuff
https://www.excelfox.com/forum/showt...ll=1#post23981
https://www.excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA-CSV-stuff?p=23981&viewfull=1#post23981
https://www.excelfox.com/forum/showt...ge12#post23981
https://www.excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA-CSV-stuff/page12#post23981
More detailed look at information from an extended property
This is the actual text copied room the text file,Here are those 7 ( 8 ) lines broken down into characters,Code:// Name: System.Size -- PKEY_Size
// Type: UInt64 -- VT_UI8
// FormatID: (FMTID_Storage) {B725F130-47EF-101A-A5F1-02608C9EEBAC}, 12 (PID_STG_SIZE)
//
//
DEFINE_PROPERTYKEY(PKEY_Size, 0xB725F130, 0x47EF, 0x101A, 0xA5, 0xF1, 0x02, 0x60, 0x8C, 0x9E, 0xEB, 0xAC, 12);
#define INIT_PKEY_Size { { 0xB725F130, 0x47EF, 0x101A, 0xA5, 0xF1, 0x02, 0x60, 0x8C, 0x9E, 0xEB, 0xAC }, 12 }
I don't see any "hidden character" surprizes, or anything else of interest or concern at this stage. So…..Code:"Size " & "-" & "-" & " PKEY" & "_" & "Size" & vbCr & vbLf
"/" & "/" & " Type" & ":" & " UInt64 " & "-" & "-" & " VT" & "_" & "UI8" & vbCr & vbLf
"/" & "/" & " FormatID" & ":" & " " & "(" & "FMTID" & "_" & "Storage" & ")" & " " & Chr(123) & "B725F130" & "-" & "47EF" & "-" & "101A" & "-" & "A5F1" & "-" & "02608C9EEBAC" & Chr(125) & "," & " 12 " & "(" & "PID" & "_" & "STG" & "_" & "SIZE" & ")" & vbCr & vbLf
"/" & "/" & vbCr & vbLf
"/" & "/" & " " & vbCr & vbLf
"DEFINE" & "_" & "PROPERTYKEY" & "(" & "PKEY" & "_" & "Size" & "," & " 0xB725F130" & "," & " 0x47EF" & "," & " 0x101A" & "," & " 0xA5" & "," & " 0xF1" & "," & " 0x02" & "," & " 0x60" & "," & " 0x8C" & "," & " 0x9E" & "," & " 0xEB" & "," & " 0xAC" & "," & " 12" & ")" & ";" & vbCr & vbLf
"#" & "define INIT" & "_" & "PKEY" & "_" & "Size " & Chr(123) & " " & Chr(123) & " 0xB725F130" & "," & " 0x47EF" & "," & " 0x101A" & "," & " 0xA5" & "," & " 0xF1" & "," & " 0x02" & "," & " 0x60" & "," & " 0x8C" & "," & " 0x9E" & "," & " 0xEB" & "," & " 0xAC " & Chr(125) & "," & " 12 " & Chr(125) & vbCr & vbLf
vbCr & vbLf
How to move on to get a list of just the property name word that we need ( in the current example that word is Size )
The last macro put the property name, ( after the System. Bit ), and all following details in a 1 dimensional array that was then conveniently pasted out into a list in a worksheet. I could forget about the worksheet list initially and then within VBA arrays efficiently get at the first word bits I wants. But text is cheap and Excel is all about ordering boxes of things into a convenient list. Furthermore I have efficient ways of manipulating lists using excel function evaluate range ways. So I think it will be convenient to keep the full text in the first column and get the name words initially efficiently in the another column
See here https://www.excelfox.com/forum/showt...ll=1#post23983
Simple text file of Propherties
Using the final file obtained there ( https://www.excelfox.com/forum/showt...ll=1#post23983 ] ) , it is convenient to make a simple text file looking like this, ( just showing the first and last few lines, ( there are 1054 in total )( Note in final use, we must include a leading System. )Code:Address.Country
Address.CountryCode
Address.Region
Address.RegionCode
Address.Town
Audio.ChannelCount
Audio.Compression
Audio.EncodingBitrate
Audio.Format
Audio.IsVariableBitRate
Audio.PeakValue
Audio.SampleRate
Audio.SampleSize
Audio.StreamName
Audio.StreamNumber
Calendar.Duration
Calendar.IsOnline
Calendar.IsRecurring
Calendar.Location
Calendar.OptionalAttendeeAddresses
Calendar.OptionalAttendeeNames
Calendar.OrganizerAddress
Calendar.OrganizerName
Calendar.ReminderTime
Calendar.RequiredAttendeeAddresses
Calendar.RequiredAttendeeNames
Calendar.Resources
Calendar.ResponseStatus
Calendar.ShowTimeAs
Calendar.ShowTimeAsText
Communication.AccountName
Communication.DateItemExpires
Communication.Direction
Communication.FollowupIconIndex
Communication.HeaderItem
Communication.PolicyTag
Communication.SecurityFlags
Communication.Suffix
Communication.TaskStatus
Communication.TaskStatusText
Computer.DecoratedFreeSpace
Contact.AccountPictureDynamicVideo
Contact.AccountPictureLarge
Contact.AccountPictureSmall
Contact.Anniversary
Contact.AssistantName
Contact.AssistantTelephone
Contact.Birthday
Contact.BusinessAddress
Contact.BusinessAddress1Country
Contact.BusinessAddress1Locality
Contact.BusinessAddress1PostalCode
Contact.BusinessAddress1Region
Contact.BusinessAddress1Street
Contact.BusinessAddress2Country
Contact.BusinessAddress2Locality
Contact.BusinessAddress2PostalCode
Contact.BusinessAddress2Region
Contact.BusinessAddress2Street
Contact.BusinessAddress3Country
Contact.BusinessAddress3Locality
Contact.BusinessAddress3PostalCode
Contact.BusinessAddress3Region
Contact.BusinessAddress3Street
Contact.BusinessAddressCity
Contact.BusinessAddressCountry
Contact.BusinessAddressPostalCode
Contact.BusinessAddressPostOfficeBox
Contact.BusinessAddressState
Contact.BusinessAddressStreet
Contact.BusinessEmailAddresses
Contact.BusinessFaxNumber
Contact.BusinessHomePage
Contact.BusinessTelephone
Contact.CallbackTelephone
Contact.CarTelephone
Contact.Children
Contact.CompanyMainTelephone
Contact.ConnectedServiceDisplayName
Contact.ConnectedServiceIdentities
Contact.ConnectedServiceName
Contact.ConnectedServiceSupportedActions
Contact.DataSuppliers
Contact.Department
Contact.DisplayBusinessPhoneNumbers
Contact.DisplayHomePhoneNumbers
Contact.DisplayMobilePhoneNumbers
Contact.DisplayOtherPhoneNumbers
Contact.EmailAddress
Contact.EmailAddress2
Contact.EmailAddress3
Contact.EmailAddresses
Contact.EmailName
Contact.FileAsName
Contact.FirstName
Contact.FullName
Contact.Gender
Contact.GenderValue
Contact.Hobbies
.
.
.
.
.
Task.Owner
Video.Compression
Video.Director
Video.EncodingBitrate
Video.FourCC
Video.FrameHeight
Video.FrameRate
Video.FrameWidth
Video.HorizontalAspectRatio
Video.IsSpherical
Video.IsStereo
Video.Orientation
Video.SampleSize
Video.StreamName
Video.StreamNumber
Video.TotalBitrate
Video.TranscodedForSync
Video.VerticalAspectRatio
Volume.FileSystem
Volume.IsMappedDrive
Using the final file obtained there ( https://www.excelfox.com/forum/showt...ll=1#post23983 ] ) , this code in the worksheet object tab name Ext(Hidden)proph
Code:' WSO_PropNamesExtended.xls https://app.box.com/s/sv5rxxtwv1v18ir3xmi6gdti8pawx0jq
Sub MakeExtProphsTextFile() ' https://www.excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA-CSV-stuff?p=23981&viewfull=1#post23981
Rem 1 Copy to Clipboard
Me.Range("E2:E1055").Copy ' Selection.Copy ' Or Application.SendKeys "^c"
With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") ' http://web.archive.org/web/20200124185244/http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/
Dim StringBack As String ' This is for the entire text held for the range in the windows clipboard after a .Copy
.GetFromClipboard: Let StringBack = .GetText()
' .Clear
' .SetText StringBack
' .PutInClipboard
End With
Let StringBack = Left(StringBack, Len(StringBack) - 2) ' Get rid of the extra vbcr & vblf caused by .copy
Rem 2
Dim FileNum2 As Long: Let FileNum2 = FreeFile(0) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName2 As String
Let PathAndFileName2 = ThisWorkbook.Path & "\ExtProphs.txt" ' ' CHANGE TO SUIT
Open PathAndFileName2 For Output As #FileNum2 ' ' Will be made if not there
Print #FileNum2, StringBack ' write out entire text file
Close #FileNum2
End Sub
Excel File with coding in:
WSO_PropNamesExtended.xls https://app.box.com/s/sv5rxxtwv1v18ir3xmi6gdti8pawx0jq
Text file made from above coding
ExtProphs.txt https://app.box.com/s/rcl6mubx42xgwh0r9rt3fxjv18i7vmxs
( Text file used previously, - the large one with all Propherty details, file obtained from the official Microsoft propkey.h, a header file in the Windows SDK stuff, https://www.eileenslounge.com/viewto...313961#p313961
propkey h.txt https://app.box.com/s/r9jx8r8qhs1g0phvg20f5penmhfetbcg )
Some notes in support of this discovery,
https://www.excelfox.com/forum/showt...ws-Based-Files -
https://www.excelfox.com/forum/showt...ws-Based-Files
For Eileen’s Lounge https://www.eileenslounge.com/viewto...314893#p314893
Hi,
Some weird co incidences have hit me in the past few years, (including some similar to those that happened to same bad people that convinced them they were the chosen one to take over the world) ….
Maybe in my case I am imagining it, or it is just Microsoft and Google taking over my PC and my mind…
So here is one, not so dramatic, but worth a follow up here , I thought:….. I wanted to take a break from my experiments with Folder item properties, (and some naughty things I had better not mention), so for some light relief I did some mundane stuff of tidying up a forum by looking at soft deleted or closed, marked as possible spam Threads from years ago, waiting for someone like me to delete them permanently or reinstate them...
Fairly soon I coincidently hit this one, in a dead Outlook sub forum of all places!? (Further strangely its posted by a "guest" in 2017. As far as I know it’s only ever been possible to post there after registering, and certainly it was the case in 2017, so it’s something akin to a virgin birth as well)
https://www.excelfox.com/forum/showt...ws-Based-Files
Now here is the strange coincidence, it’s a list of the names of "All Extended Property Of Windows Based Files" , which is what is related to what has been uppermost in my mind just now, and the main subject of this Thread.
They are given in that strange {B725F130-47EF-101A-A5F1-02608C9EEBAC}, 14 form.
Now I remember a long time ago in a Thread at Eileen’s Lounge I did something like running them sort of things in PowerShell, (I can’t remember why now, but since then I get a lot of new applications starting when I restart the computer I did it on??)
So I am spending the rest of the day, or days, trying to find that old Thread, because I am thinking that God maybe had suggested I do it, in his mysterious way of moving and doing things.
In the meantime I thought I would share this information, in case anyone has any thoughts on:
(_ if I am the chosen one )
_ how I might be able to somehow do things with these to get a file property information.? Possibly this weird variation on a theme might give another interesting solution to this thread, or point me in some useful direction. Or could I do anything else with them? For example, could these or what they might refer to help me to put things, programs, dlls things or the such, somewhere that might help me solve my recent related XP problem ?
I put those things in a text file, ( with and without the headings ). There are 154, which is less than both the main two ways discussed here so far,
the few hundred WSO Properties ( like from objWSOFolder.GetDetailsOf(FldrItm, 1) )
, and
the 1054 WSO Propherkeys (correspondingly like objWSOPassName.ExtendedProperty("System." & "Size") )
, so I am not sure which of those two, if any, they may be related to?
Thanks
Alan
Reply from SpeakEasy, https://www.eileenslounge.com/viewto...314905#p314905
Quote:
>They are given in that strange form
These are SCIDs, an alternative method of using ExtendedProperty.
So, if you examine the propkey.h file I pointed you to previously, you will see these 'strange' numbers as well as the [fx]System. [/fx]name strings. They are an alternate method to access the property, and basically consist of GUID representing the Format ID (FMTID) defining a property set, and a PID that identifies a specific property within that set. Together these make up what Microsoft refer to as a SCID, and can be passed to ExtendedProperty as a string instead of the human-readable string
Here for example is the propkey.h entry for System.Size, with the SCID highlighted
[fx]// Name: System.Size -- PKEY_Size
// Type: UInt64 -- VT_UI8
// FormatID: (FMTID_Storage) {B725F130-47EF-101A-A5F1-02608C9EEBAC}, 12 (PID_STG_SIZE)
//
//
DEFINE_PROPERTYKEY(PKEY_Size, 0xB725F130, 0x47EF, 0x101A, 0xA5, 0xF1, 0x02, 0x60, 0x8C, 0x9E, 0xEB, 0xAC, 12);
#define INIT_PKEY_Size { { 0xB725F130, 0x47EF, 0x101A, 0xA5, 0xF1, 0x02, 0x60, 0x8C, 0x9E, 0xEB, 0xAC }, 12 }[/fx]
And here is how we can use it
(I don't expect this will fix your XP problem)Code:Dim objShell As New Shell
With objShell.Namespace("D:\downloads\deletemesoon").ParseName("20220501_103054.jpg") ' your path and filename go here
Debug.Print "Accurate size: " & .ExtendedProperty("System.Size")
Debug.Print "Accurate size: " & .ExtendedProperty("{B725F130-47EF-101A-A5F1-02608C9EEBAC}, 12")
End With
Reply to last post, (Reply to SpeakEasy), Uncensored Version
Thanks for all that. Interesting. I expect I will come back here often and ponder all that.Quote:
Originally Posted by SpeakEasy
I have come across the {weird number in curly bracket unique identifier GUID things}, - I am not so totally sure what they are about but have used them and messed with them in a thread I am still looking for. ( I also have used them as a way to Late bind, and never really got the point so good, and I think in the thread I am looking for, I was randomly finding them on the registry and messing with them trying to figure out what they are about or what they did. (Often they seemed to "initiate" things on my computer to always start, things I never knew I had)
It’s all a bit confusing for me, all these new terms, but never the less very helpful to have it all here as I expect eventually it may all fall in to place, when I re read, discover things, and keep experimenting, etc.
I had spent some time already looking in detail at the text like file you pointed me to. I examined it carefully to see exactly what characters are in it. ( https://www.excelfox.com/forum/showt...ge12#post23981 You do get what you see, - there are no strange "hidden" characters in it), so I made notes on it, isolated the names and went on to experiment using the name bits from it.
( https://www.excelfox.com/forum/showt...ge59#post23983
https://www.excelfox.com/forum/showt...ge4#post239729
I was going to reference all that in some concluding feedback here, but then got unexpectedly stuck on the XP issue.
I had pulled out a list of all 1054 names to put in .ExtendedProperty("System.name")
, so I the am going to go back to that now and pull out the SCIDs , ( I am first getting my masks, disinfectant, surgical gloves etc. ready, - googling tells me SCID is some weakness in immune system caused by playing chess on computers, - could be some early experiments of Bill Gates maybe, to distribute viruses in operating systems which finally led to the Coronavirus)
I had not noticed yet that that the GUIDs in that forum post I found tied up with the ones in the propkey h text file thing. I see now they do, thanks for the heads up. (These GUID things seem to have their ugly head all over the place so my first reaction was to ignore the ones in the propkey h text file thing , Lol.? )
( It’s possibly falling into place now, I see now that the mysterious forum post I found was possibly planted in by a forth columnist working for Microsoft or Bill Gates. I expect getting rid of that post will be like trying to cut out Microsoft Edge. I may have to just try to quarantine/ isolate it somehow, or limit its resources to contain it a bit)
Thanks for the enlightening reply,
Alan
P.S. A bit of Laymen lateral thinking… These GUIDs refer to other stuff, often I think some sort of sub programs, libraries of stuff, including perhaps functions / programs, ( dll and COM codswallops & co ) that may or may not be available. I wonder if when I look now at the relevant GUIDs , then me or someone smarter may be able to identify some "package/ download cabinet or kitchen sink" or whatever, that I am missing on my XP machines that is causing my XP problem ??
This is post
https://www.excelfox.com/forum/showt...ll=1#post24047
https://www.excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA-CSV-stuff?p=24047&viewfull=1#post24047
https://www.excelfox.com/forum/showt...ge12#post24047
https://www.excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA-CSV-stuff/page12#post24047
Following on from the last post
Later, after getting me morning BunsQuote:
Originally Posted by DocAElstein
Code:
Address.Country -- PKEY_Address_Country
// Type: String -- VT_LPWSTR (For variants: VT_BSTR)
// FormatID: {C07B4199-E1DF-4493-B1E1-DE5946FB58F8}, 100
DEFINE_PROPERTYKEY(PKEY_Address_Country, 0xC07B4199, 0xE1DF, 0x4493, 0xB1, 0xE1, 0xDE, 0x59, 0x46, 0xFB, 0x58, 0xF8, 100);
#define INIT_PKEY_Address_Country { { 0xC07B4199, 0xE1DF, 0x4493, 0xB1, 0xE1, 0xDE, 0x59, 0x46, 0xFB, 0x58, 0xF8 }, 100 }
"Address.Country -- PKEY_Address_Country
// Type: String -- VT_LPWSTR (For variants: VT_BSTR)
// FormatID: {C07B4199-E1DF-4493-B1E1-DE5946FB58F8}, 100
DEFINE_PROPERTYKEY(PKEY_Address_Country, 0xC07B4199, 0xE1DF, 0x4493, 0xB1, 0xE1, 0xDE, 0x59, 0x46, 0xFB, 0x58, 0xF8, 100);
#define INIT_PKEY_Address_Country { { 0xC07B4199, 0xE1DF, 0x4493, 0xB1, 0xE1, 0xDE, 0x59, 0x46, 0xFB, 0x58, 0xF8 }, 100 }
"
"Audio.Compression -- PKEY_Audio_Compression
// Type: String -- VT_LPWSTR (For variants: VT_BSTR)
// FormatID: (FMTID_AudioSummaryInformation) {64440490-4C8B-11D1-8B70-080036B11A03}, 10 (PIDASI_COMPRESSION)
//
//
DEFINE_PROPERTYKEY(PKEY_Audio_Compression, 0x64440490, 0x4C8B, 0x11D1, 0x8B, 0x70, 0x08, 0x00, 0x36, 0xB1, 0x1A, 0x03, 10);
#define INIT_PKEY_Audio_Compression { { 0x64440490, 0x4C8B, 0x11D1, 0x8B, 0x70, 0x08, 0x00, 0x36, 0xB1, 0x1A, 0x03 }, 10 }
"
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314619#p314619
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314600#p314600
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314599#p314599
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314274#p314274
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314229#p314229
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314195#p314195
https://www.eileenslounge.com/viewtopic.php?f=36&t=39706&p=314110#p314110
https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314081#p314081
https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314078#p314078
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314062#p314062
https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314054#p314054
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313971#p313971
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313909#p313909
https://www.eileenslounge.com/viewtopic.php?f=27&t=40574&p=313879#p313879
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313859#p313859
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313855#p313855
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313848#p313848
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313843#p313843
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313792#p313792
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313771#p313771
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313767#p313767
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313746#p313746
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313744#p313744
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313741#p313741
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313622#p313622
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313575#p313575
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313573#p313573
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313563#p313563
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313555#p313555
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533
https://www.eileenslounge.com/viewtopic.php?f=39&t=40265&p=313468#p313468
https://www.eileenslounge.com/viewtopic.php?f=42&t=40505&p=313411#p313411
https://www.eileenslounge.com/viewtopic.php?f=32&t=40473&p=313384#p313384
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
Notes in support of this post
http://www.eileenslounge.com/viewtop...325398#p325398
https://eileenslounge.com/viewtopic....325403#p325403
https://eileenslounge.com/viewtopic....325413#p325413
From here, http://www.eileenslounge.com/viewtop...325398#p325398 , the same link has been given twice
https://drive.google.com/drive/folders/1-ERBo_Rd66MfBztr1jMZuXWKqHYfF6fp?usp=sharing
https://drive.google.com/drive/folders/1-ERBo_Rd66MfBztr1jMZuXWKqHYfF6fp?usp=sharing
There are two files, pdf file , and a current offering of a text file
https://i.postimg.cc/MvXkVZSh/Google-download-big.jpg
The text file is uploaded. Its original name from the OP was diabetes-27-apr-2017-to-23-april-2018.csv I have changed it to a .txt file
It is not a Comma Separated Values file. It has been converted somehow from a pdf file.
It appears to have a line separator of vbLf und separation of values is just by spaces.
The OP then gave access to all his files https://eileenslounge.com/viewtopic....325408#p325408 That zip file is uploaded here
I examined them and a few are comma separated files:
Yes = It is a comma separated value file
No = It is NOT a comma separated file
13-diabetes 27 apr 2017 to 23 april 2018.csv Yes
13-diabetes-27-apr-2017-to-23-april-2018.csv No
17-diabetes 27 apr 2017 to 23 april 2018.csv Yes
21-diabetes-27-apr-2017-to-23-april-2018.csv No
25-diabetes-27-apr-2017-to-23-april-2018.csv No
29-diabetes-27-apr-2017-to-23-april-2018.csv No
33-diabetes 27 apr 2017 to 23 april 2018_1.csv Yes (but there are many corruptions in it )
37-diabetes-27-apr-2017-to-23-april-2018.csv No
41-diabetes-27-apr-2017-to-23-april-2018.csv No
45-diabetes-27-apr-2017-to-23-april-2018 (1).csv No
45-diabetes-27-apr-2017-to-23-april-2018.csv No
5-diabetes-27-apr-2017-to-23-april-2018.csv No
5-diabetes-27-apr-2017-to-23-april-2018_1.csv No
9-diabetes 27 apr 2017 to 23 april 2018.csv Yes
9-diabetes-27-apr-2017-to-23-april-2018.csv No
diabetes 27 apr 2017 to 23 april 2018.pdf pdf file
diabetes-27-apr-2017-to-23-april-2018.csv No
I have looked extensively manually into diabetes-27-apr-2017-to-23-april-2018.csv. The formatting is a bit inconsistent, and also a bit quirky, it looks different in different text editors:
https://i.postimg.cc/ZqXVPmCS/Differ...in11-Vista.jpg
https://i.postimg.cc/yJXmBwmQ/Differ...in11-Vista.jpg
_.________________________________________________ ______________________________
Excel Files from pdf attempt:- https://app.box.com/s/ywwjzsk168pbdbf7r8twastu52cdhaaf
Share ‘diabetes 27 apr 2017 to 23 april 2018.doc’ https://app.box.com/s/7w7xtmj4owlbs3tttga38xroom1q2gtf
Share ‘diabetes 27 apr 2017 to 23 april 2018.pdf’ https://app.box.com/s/5xby7eoay85p4vxk3xi4ai8dfpi0qivs
Share ‘diabetes-27-apr-2017-to-23-april-2018 from OP google drive Feb 22 saved as Excel file.xls’ https://app.box.com/s/9997gjd7qfqr9jq8716u53gsnlh0ev13
Share ‘diabetes-27-apr-2017-to-23-april-2018 from OP google drive Feb 22.txt’ https://app.box.com/s/csuc5x7m0b4d8b9nbkzx0m4e9n6y1fr7
Share ‘diabetes-27-apr-2017-to-23-april-2018 with vbCr & vbLf.txt’ https://app.box.com/s/mu43fiicp3fxdmea7q6bq346wecstm68