Amend open Workbook code

  • Thread starter Thread starter Stuart
  • Start date Start date
S

Stuart

This statement opens a 'text' file:
Workbooks.OpenText Filename:= _
"C:\BofQProject\Cato Import\B005A__1.EBQ", _
Origin:=xlWindows, StartRow:=13, _
DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(13, 2), Array(71, 1), _
Array(73, 1), Array(109, 1))

I have this code which will get all files in a chosen folder:
FName = (SourceDir & "\" & "*.EBQ")
FName = Dir(FName)
Do While Len(FName) > 0
FileCounter = FileCounter + 1
ReDim Preserve FilesArray(1 To FileCounter)
FilesArray(FileCounter) = FName
FName = Dir()
Loop

How do I amend the following to open each workbook
in turn, please?

If FileCounter > 0 Then
Application.ScreenUpdating = False
For LoopCounter = 1 To FileCounter
Workbooks.Open vFilename & FilesArray(LoopCounter), False

Regards.
 
Stuart,

Do you just want to open them?

If FileCounter > 0 Then
Application.ScreenUpdating = False
For LoopCounter = 1 To FileCounter
Workbooks.Open vFilename & FilesArray(LoopCounter), False
Next
End If

Not sure what vFilename is


Or do you want to open them as text files?

If FileCounter > 0 Then
Application.ScreenUpdating = False
For LoopCounter = 1 To FileCounter
Workbooks.OpenText Filename:= _
FilesArray(LoopCounter), _
Origin:=xlWindows, StartRow:=13, _
DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(13, 2), Array(71, 1), _
Array(73, 1), Array(109, 1))
Next
End If


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
vFilename = SourceDir & "\"
FName = (SourceDir & "\" & "*.EBQ")
FName = Dir(FName)
Do While Len(FName) > 0
FileCounter = FileCounter + 1
ReDim Preserve FilesArray(1 To FileCounter)
FilesArray(FileCounter) = FName
FName = Dir()
Loop
If FileCounter > 0 Then
Application.ScreenUpdating = False
For LoopCounter = 1 To FileCounter
Workbooks.OpenText Filename:= _
vFilename & FilesArray(LoopCounter), _
Origin:=xlWindows, StartRow:=13, _
DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(13, 2), Array(71, 1), _
Array(73, 1), Array(109, 1))
Next
Application.ScreenUpdating = True
End If
 
Need to open them as text files, but in that
specific delimited way.

Many thanks to you both.

Regards.
 
Back
Top