Opening Workbooks / Filling Array

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bill - 19 Jan 2006 19:33 GMT

I use the code below to open workbooks and fill and array. Unfortunately the
workbook names change. I have 50 workbooks that I extract data from. Can
someone please assist me in adjusting the code so that is opens the books
regardless of the names and then fills the array regardless of the name.

' Opens the Tracker workbooks for each unit
Dim strPath As String
strPath = "\\sphere\Obit\"
Workbooks.Open strPath & "D40.xls"
Workbooks.Open strPath & "DLP.xls"
Workbooks.Open strPath & "WS_234.xls"
Workbooks.Open strPath & "POD.xls"
Workbooks.Open strPath & "POD2.xls"
Workbooks.Open strPath & "GLOB34.xls"

' Establishes an array
Dim Tracker_Array As Variant
Tracker_Array = Array("D40.xls", "DLP.xls", "WS_234.xls", "POD.xls",
"POD2.xls", "GLOB34.xls”)

Thanks for your assistance

Bill

Reply to this Message

Leith Ross - 19 Jan 2006 20:19 GMT

Hello Bill,

This macro will create a variant array of all files in the director
you choose and of the type you specify.

Your macro would look like this...
Sub MyMacro()
Dim Files
Files = GetFileList("\\sphere\Obit\", "xls")
For I = 1 To Files(0) 'The zero element holds the File count
Workbooks(Files(I)).Open
Next I
TrackerArray = Files()
End Sub

Get Files Macro Code:

Public Function GetFileList(ByVal File_Directory As String, ByVa
File_Type As String) As Variant

Dim FileCount As Long
Dim FileList() As String
Dim FileName As String

FileName = Dir(File_Directory & "\*." & File_Type)
ReDim FileList(0)

Do While FileName <> ""
FileCount = FileCount + 1
ReDim Preserve FileList(FileCount)
FileList(FileCount) = FileName
FileName = Dir
Loop

FileList(0) = FileCount

GetFileList = FileList()

End Function

Sincerely,
Leith Ros

Reply to this Message