Using FieldInfo with variable array content

[Dartguru]

Hi, I'm new here and have scoured the Excel Help forum before posting, but cannot see a solution to my problem.

Briefly, I have a macro which opens two text files as attached.

So for each file, the only difference is the tabbing required for fieldinfo. Originally, I recorded macros to sort out how they worked but, as there are going to ultimately about fifty different files, I've tried to "macroise" it.

The error I get is unsurprisingly Run-Time error 1004, Method 'OpenText' of object 'Workbooks' failed.

So, if I want to create an array to hold the tab stops, how do I do it?

TIA

[Bob Phillips]

Where is your code?

[Dartguru]

Was a code extract not attached, temp.zip?
I can see it when I look.
I've included it below. I think the problem is to do with how I've set up the iTabStop array, as I'm not defining the value of each y-element.



Public Sub GetNewAnalysis()

Dim sMasterFile As String
Dim sBaseLogDir As String
Dim sStdLogFileName As String
Dim sFullFileSpec As String

Dim iOS As Integer

Dim iTabStop() As Integer
Dim iTabTotal As Integer

sBaseLogDir = "D:\logs"

sMasterFile = ActiveWorkbook.Name

' process for each log file
For iOS = 1 To 2
Select Case iOS
Case 1
sStdLogFileName = "File1.txt"
iTabTotal = 5
ReDim iTabStop(iTabTotal, 4)
iTabStop(1, 1) = 0
iTabStop(2, 1) = 15
iTabStop(3, 1) = 45
iTabStop(4, 1) = 65
iTabStop(5, 1) = 67
Case 2
sStdLogFileName = "File2.txt"
iTabTotal = 8
ReDim iTabStop(iTabTotal, 4)
iTabStop(1, 1) = 0
iTabStop(2, 1) = 15
iTabStop(3, 1) = 39
iTabStop(4, 1) = 46
iTabStop(5, 1) = 88
iTabStop(6, 1) = 95
iTabStop(7, 1) = 103
iTabStop(8, 1) = 116
Case Else
' do nothing
End Select

sFullFileSpec = D:\logs\" & sStdLogFileName

' open log file
Workbooks.OpenText Filename:= _
sFullFileSpec, _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=iTabStop, _
TrailingMinusNumbers:=True

...

Next iOS

End Sub

[KSN]

Hi Dartguru
I have almost the exact same need. Did you finally find a solution to this problem?
Regards
KSN

[Bob Phillips]

I thought that was a zip of the text files to be read :)




Public Sub GetNewAnalysis()
Dim sMasterFile As String
Dim sBaseLogDir As String
Dim sStdLogFileName As String
Dim sFullFileSpec As String
Dim iOS As Integer
Dim iTabStop() As Variant

sBaseLogDir = "D:\logs\"
sMasterFile = ActiveWorkbook.Name
'process for each log file

For iOS = 1 To 2
Select Case iOS
Case 1
sStdLogFileName = "File1.txt"
ReDim iTabStop(1 To 5)
iTabStop(1) = Array(0, 1)
iTabStop(2) = Array(15, 1)
iTabStop(3) = Array(45, 1)
iTabStop(4) = Array(65, 1)
iTabStop(5) = Array(67, 1)
Case 2
sStdLogFileName = "File2.txt"
ReDim iTabStop(1 To 8)
iTabStop(1) = Array(0, 1)
iTabStop(2) = Array(15, 1)
iTabStop(3) = Array(39, 1)
iTabStop(4) = Array(46, 1)
iTabStop(5) = Array(88, 1)
iTabStop(6) = Array(95, 1)
iTabStop(7) = Array(103, 1)
iTabStop(8) = Array(116, 1)
Case Else
'do nothing

End Select
sFullFileSpec = sBaseLogDir & sStdLogFileName
'open log file

Workbooks.OpenText Filename:=sFullFileSpec, _
Origin:=xlMSDOS, _
StartRow:=1, _
DataType:=xlFixedWidth, _
FieldInfo:=iTabStop, _
TrailingMinusNumbers:=True
Next iOS
End Sub

[KSN]

Dear xld
Thanks very much for the post. From your example I discovered what was required to resolve the problem that I had.

I had posted another thread with the title "Text To Columns - passing FieldInfo value as a string". I had given the code there as:




Sub test()
Dim i as long
Dim FieldInfoVal As String
Dim DTA_DataStartCol as Long
DTA_DataStartCol = 17 ' This will be a dynamic value to be picked up run time - 17 is just an example for testing this piece of code
FieldInfoVal = ""
For i = 1 To DTA_DataStartCol
If i <> DTA_DataStartCol Then
FieldInfoVal = FieldInfoVal & "Array(" & i & ", 2),"
Else: FieldInfoVal = FieldInfoVal & "Array(" & i & ", 2)"
End If
Next i
' The commented out code below is what is sought to be achieved i.e. 1st 17 columns are treated as Text
' Selection.TextToColumns DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, _
2), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2), Array(10, 2), Array(11, 2), Array(12 _
, 2), Array(13, 2), Array(14, 2), Array(15, 2), Array(16, 2), Array(17, 2)), _
TrailingMinusNumbers:=True

' The below code is the attempt to provide the Array as a string (FieldInfoVal)
Selection.TextToColumns DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(FieldInfoVal), _
TrailingMinusNumbers:=True


End Sub

I made the following changes:

Dim FieldInfoVal As String
change as Dim FieldInfoVal As Variant

Add the following line:
Redim FieldInfoVal(1 to DTA_DataStartCol)


Change the lines below:

For i = 1 To DTA_DataStartCol
If i <> DTA_DataStartCol Then
FieldInfoVal = FieldInfoVal & "Array(" & i & ", 2),"
Else: FieldInfoVal = FieldInfoVal & "Array(" & i & ", 2)"
End If
Next i

To
For i = 1 To DTA_DataStartCol
FieldInfoVal(i) = Array(i, 2)
Next i

And finally, change the TextToColumns method line to:

Selection.TextToColumns DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=FieldInfoVal, _
TrailingMinusNumbers:=True


This works just fine - thanks so much xld for the help.

Regards
KSN

[Dartguru]

Hi KSN,
apologies for the bad form of not posting a fix when I'd figured it out. It looks like youre've sorted it now but, for reference, below is what I did..

Where I had the one redimensioned array - incorrectly redimensioned as it turned out, I corrected this and addde two further arrays from which the data could be correctly loaded, see attached.