VB help with ExecuteExcel4Macro

[cmjat]

Hi,

I'd like to use ExecuteExcel4Macro to help me get data from closed workbooks
but I'm definitely a novice when it comes to VB. I have a folder with a lot
of workbooks I need to pull data from and the data is all in the same places,
meaning, for example, on the UPLOAD tab, Row 2, Cells A-AA, but I only need
data from some of the workbooks in the folder. I have a list of job names and
for every job name there is a workbook so I somehow need to do-while for the
list of job names that I can put in a column. For example, I have data I can
paste into Excel that looks like this:

QC Record : Job Name : Listing Count : Date
201002-18485-QC : HD-11311-TA031110 : 263 : 3/15/10
201003-15737-QC : SHT-100312 : 103 : 3/14/10
201002-18327-QC : 032110Mag : 246 : 3/17/10
201001-18510-QC: TSA-100321 : 411 : 3/15/10

For every job name there exists a file called, for example,
\\de-mt1\clients\QC Reports\Proofs Adjusted\HD-100311-TA031110 proof
adjusted.xls where HD-100311-TA031110 is the job name.

I need help with the VB in ExecuteExcel4Macro at two places: 1) where it
opens the files as I described above and 2) I'm not sure how to only pull the
cells I need data for.

Can you help?

 

[Peter T]

I would suggest a different approach. See Ron de Bruin's ADO page, I think
the example file will cover what you are looking for

http://www.rondebruin.nl/ado.htm

Regards,
Peter T

 

[cmjat]

My understand is ADO doesn't work with mixed data types and all my data is
mixed.

Job Name
HD-11311-TA031110
SHT-100312
032110Mag
TSA-100321

 

[Peter T]

I take it you didn't try it!

Regards,
Peter T

My understand is ADO doesn't work with mixed data types and all my data is
mixed.

Job Name
HD-11311-TA031110
SHT-100312
032110Mag
TSA-100321

 

[cmjat]

You're right, I didn't but I did some more reading and I see the error of my
ways. Unfortunately this isn't high on the list of priorities and I've been
having trouble actually getting to the work.

I downloaded Ron de Bruin's merge sample and I'm wondering how, instead of
having the function Get_File_Names fill an array with the filenames if I can
preload this information since I will know the list of filename I will want
to pull data from and the folder will have more files than the subset of ones
I want.

Any ideas? I'm a VB novice so I can see what the code is generally doing and
make minor modifications but I don't think I could write anything meaningful
from scratch.

Thanks!

 

[Peter T]

If I follow the function Get_File_Names would appear to be a function in Ron
de Bruins addin RDBMerge.xla. But the password is locked so I guess you are
referring to some other file I am not aware of. Advise which file and link
you are referring to.

Regards,
Peter T

 

[cmjat]

Hi Peter,

You directed me to http://www.rondebruin.nl/ado.htm where there's a sample
workbook which can be downloaded called MergeExamples

His explanation of the function I'm referring to is:
'First we call the Function "Get_File_Names" to fill a array with all file
names
'There are three arguments in this Function that we can change

'1) MyPath = the folder where the files are
'2) Subfolders = True if you want to include subfolders
'3) ExtStr = file extension of the files you want to merge
' ExtStr examples are: "*.xls" , "*.csv" , "*.xlsx"
' "*.xlsm" ,"*.xlsb" , for all Excel file formats use "*.xl*"
' Do not change myReturnedFiles:=myFiles

The code of that function is:

Function Get_File_Names(MyPath As String, Subfolders As Boolean, _
ExtStr As String, myReturnedFiles As Variant) As Long

Dim Fso_Obj As Object, RootFolder As Object
Dim SubFolderInRoot As Object, file As Object

'Add a slash at the end if the user forget it
If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
End If

'Create FileSystemObject object
Set Fso_Obj = CreateObject("Scripting.FileSystemObject")

Erase myFiles()
Fnum = 0

'Test if the folder exist and set RootFolder
If Fso_Obj.FolderExists(MyPath) = False Then
Exit Function
End If
Set RootFolder = Fso_Obj.GetFolder(MyPath)

'Fill the array(myFiles)with the list of Excel files in the folder(s)
'Loop through the files in the RootFolder
For Each file In RootFolder.Files
If LCase(file.Name) Like LCase(ExtStr) Then
Fnum = Fnum + 1
ReDim Preserve myFiles(1 To Fnum)
myFiles(Fnum) = MyPath & file.Name
End If
Next file

'Loop through the files in the Sub Folders if SubFolders = True
If Subfolders Then
Call ListFilesInSubfolders(OfFolder:=RootFolder, FileExt:=ExtStr)
End If

myReturnedFiles = myFiles
Get_File_Names = Fnum
End Function


Sub ListFilesInSubfolders(OfFolder As Object, FileExt As String)
'Origenal SubFolder code from Chip Pearson
'http://www.cpearson.com/Excel/RecursionAndFSO.htm
'Changed by Ron de Bruin, 27-March-2008
Dim SubFolder As Object
Dim fileInSubfolder As Object

For Each SubFolder In OfFolder.Subfolders
ListFilesInSubfolders OfFolder:=SubFolder, FileExt:=FileExt

For Each fileInSubfolder In SubFolder.Files
If LCase(fileInSubfolder.Name) Like LCase(FileExt) Then
Fnum = Fnum + 1
ReDim Preserve myFiles(1 To Fnum)
myFiles(Fnum) = SubFolder & "\" & fileInSubfolder.Name
End If
Next fileInSubfolder

Next SubFolder
End Sub

Ideally, I'd like to have the code open all the files I've loaded into a
column on my worksheet instead of all the files in the directory.

Sorry about being so vague.

 

[Peter T]

I can't see the file MergeExamples on that page for looking, but not to
worry.

If your files are already loaded into cells simply do something like this



Dim i As Long
Dim arrFiles(), v

arrFiles = ActiveWorkbook.Worksheets("Sheet1").Range("A1:A10").Value

ReDim myFiles(1 To UBound(arrFiles))
i = 0
For Each v In arrFiles
i = i + 1
myFiles(i) = v
Next

From Ron's code, although you didn't include it I assume myFiles() is a
string array declared at module level, at the top of the module -

Private myFiles() As String ' or maybe it's Public

Obviously adjust the range reference to suit. You could either make this a
separate function say named Get_File_Names. If the range reference is
static the function doesn't need any arguments, otherwise include whatever
you need to build the range.

Regards,
Peter T