I can't wrap my head around this. I have 2 slightly different versions of a macro: GetDataArray and GetDataArray2.
One works fine. The other does not, behaves oddly, hoping someone can help. In both cases the code is importing data from text files to excel using keywords, using binary translation etc… its all a little over my head so I’m hoping there’s an obvious reason that I just don’t understand.
Both macros are in the same workbook running excel 2010. Both of them refer to text files saved in the same folder/directory, I'm just trying to do same thing with 2 different text files.
I think it has something to do with this line:
because something different happens if I change to
or to
. But in any case its not transferring the text from the .txt to the .xlsm
It looks to me like the keyword the macro is searching for is clearly there in both instances. Perhaps there's something different about the 2 text files?
I’ve attached them both here in hopes that someone will see something I am not seeing.
Oh, and to make it worse, the macro that does work (GetDataArray) only works on my work PC. Neither GetDataArray or GetDataArray2 works on my home laptop saving to local disk) or through citrix remote connect to my work (saving to shared network drive). I’m on excel 2010 on all 3 platforms. Any ideas on that one would be helpful but if I can get it to work on one place that's probably good enough for now.
Any direction or help is appreciated!
Here's the code that works.
And here is the version that does not work:
One works fine. The other does not, behaves oddly, hoping someone can help. In both cases the code is importing data from text files to excel using keywords, using binary translation etc… its all a little over my head so I’m hoping there’s an obvious reason that I just don’t understand.
Both macros are in the same workbook running excel 2010. Both of them refer to text files saved in the same folder/directory, I'm just trying to do same thing with 2 different text files.
I think it has something to do with this line:
Code:
Line = Split(Text, vbCrLf) 'this line gives me subscript out of range error
Code:
Line = Split(Text, vbLf) 'If I switch to this version, no error but it does not find the keyword in the text even though it is there.
Code:
Line = Split(Text) ' same thing here. Runs through all lines without finding the keyword.It looks to me like the keyword the macro is searching for is clearly there in both instances. Perhaps there's something different about the 2 text files?
I’ve attached them both here in hopes that someone will see something I am not seeing.
Oh, and to make it worse, the macro that does work (GetDataArray) only works on my work PC. Neither GetDataArray or GetDataArray2 works on my home laptop saving to local disk) or through citrix remote connect to my work (saving to shared network drive). I’m on excel 2010 on all 3 platforms. Any ideas on that one would be helpful but if I can get it to work on one place that's probably good enough for now.
Any direction or help is appreciated!
Here's the code that works.
Code:
Option Compare Text
'''''''''''''''''''''''''this code works on text file 326647_ThisFileWorks.txt''''''''''''''''''''''''''''''''''''''''''''''
Sub GetDataArray()
Dim WS As Worksheet, NextRow As Long
Dim Data() As Byte
Dim FolderPath As Variant, Lines As Variant, oFile As Variant
Dim oFiles As Object, oFolder As Object, oShell As Object
Dim Text As String
Dim Temp As Variant
Dim Symbols As String
Dim Param As Range
Dim SearchParam As Variant
Dim A As Long
'Symbols = "Si28,Si29"
'SearchParam = Split(Symbols, ".")
Set WS = Worksheets("Sheet1")
Set oShell = CreateObject("Shell.Application")
' Add fixed path to the folder.
FolderPath = "\\dfs\fs\users\asayre\ASG internal\A1 - new macros"
' Let User choose the Folder.
'Set oFolder = oShell.BrowseForFolder(0&, "Please Select the Folder with the Note Files.", 17)
'If oFolder Is Nothing Then Exit Sub Else FolderPath = oFolder.Self.Path
Set oFolder = oShell.Namespace(FolderPath)
Set oFiles = oFolder.Items
oFiles.Filter 64, "326647_ThisFileWorks.txt"
'oFiles.Filter 64, "*.csv;*.txt"
For Each oFile In oFiles
Open FolderPath & "\" & oFile For Binary Access Read As #1
ReDim Data(LOF(1))
Get #1, , Data
Close #1
Text = StrConv(Data, vbUnicode)
With WS
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
If NextRow < 3 Then NextRow = 3
End With
Line = Split(Text, vbCrLf)
Do
A = A + 1
If InStr(Line(A), "Time [sec]") > 0 Then
A = A + 2
Do
Temp = Split(Line(A), vbTab)
If Temp(0) = "" Then Exit Do
WS.Range("A" & NextRow) = Temp(0)
WS.Range("B" & NextRow) = oFile
WS.Range("C" & NextRow) = Temp(7)
WS.Range("D" & NextRow) = Temp(8)
A = A + 1
NextRow = NextRow + 1
Loop Until A = UBound(Line)
End If
Loop Until A = UBound(Line)
Next oFile
End SubAnd here is the version that does not work:
Code:
Option Compare Text
''''''''''''''''''''''''''''''this code does not work on text file zfs_ThisFile_does_not_work.txt'''''''''''''''''''''''
Sub GetDataArray2()
Dim WS As Worksheet, NextRow As Long
Dim Data() As Byte
Dim FolderPath As Variant, Lines As Variant, oFile As Variant
Dim oFiles As Object, oFolder As Object, oShell As Object
Dim Text As String
Dim Temp As Variant
Dim Symbols As String
Dim Param As Range
Dim SearchParam As Variant
Dim A As Long
Symbols = "Si28,Si29"
SearchParam = Split(Symbols, ".")
Set WS = Worksheets("Sheet1")
Set oShell = CreateObject("Shell.Application")
' Add fixed path to the folder.
FolderPath = "\\dfs\fs\users\asayre\ASG internal\A1 - new macros"
' Let User choose the Folder.
'Set oFolder = oShell.BrowseForFolder(0&, "Please Select the Folder with the Note Files.", 17)
'If oFolder Is Nothing Then Exit Sub Else FolderPath = oFolder.Self.Path
Set oFolder = oShell.Namespace(FolderPath)
Set oFiles = oFolder.Items
oFiles.Filter 64, "zfs_ThisFile_does_not_work.txt"
'oFiles.Filter 64, "zfs_ThisFile_does_not_work.txt"
'oFiles.Filter 64, "*.csv;*.txt"
For Each oFile In oFiles
Open FolderPath & "\" & oFile For Binary Access Read As #1
ReDim Data(LOF(1))
Get #1, , Data
Close #1
Text = StrConv(Data, vbUnicode)
With WS
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
If NextRow < 3 Then NextRow = 3
End With
Line = Split(Text, vbCrLf) 'this line gives me subscript out of range error
'Line = Split(Text, vbLf) 'If I switch to this version, no error but it does not find the keyword in the text even though it is there.
'Line = Split(Text) ' same thing here. Runs through all lines without finding the keyword.
Do
A = A + 1
If InStr(Line(A), "NAME") > 0 Then
A = A + 2
Do
Temp = Split(Line(A), vbTab)
If Temp(0) = "" Then Exit Do
WS.Range("A" & NextRow) = Temp(0)
WS.Range("B" & NextRow) = oFile
WS.Range("C" & NextRow) = Temp(7)
WS.Range("D" & NextRow) = Temp(8)
A = A + 1
NextRow = NextRow + 1
Loop Until A = UBound(Line)
End If
Loop Until A = UBound(Line)
Next oFile
End Sub
