Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB help with ExecuteExcel4Macro
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? --- Jen |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB help with ExecuteExcel4Macro
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" wrote in message ... 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? --- Jen |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB help with ExecuteExcel4Macro
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 -- Jen "Peter T" wrote: 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" wrote in message ... 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? --- Jen . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB help with ExecuteExcel4Macro
I take it you didn't try it!
Regards, Peter T "cmjat" wrote in message ... 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 -- Jen "Peter T" wrote: 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" wrote in message ... 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? --- Jen . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB help with ExecuteExcel4Macro
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! -- Jen "Peter T" wrote: I take it you didn't try it! Regards, Peter T "cmjat" wrote in message ... 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 -- Jen "Peter T" wrote: 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" wrote in message ... 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? --- Jen . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB help with ExecuteExcel4Macro
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" wrote in message ... 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! -- Jen "Peter T" wrote: I take it you didn't try it! Regards, Peter T "cmjat" wrote in message ... 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 -- Jen "Peter T" wrote: 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" wrote in message ... 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? --- Jen . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB help with ExecuteExcel4Macro
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 a "*.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. -- Jen "Peter T" wrote: 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" wrote in message ... 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! -- Jen "Peter T" wrote: I take it you didn't try it! Regards, Peter T "cmjat" wrote in message ... 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 -- Jen "Peter T" wrote: 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" wrote in message ... 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? --- Jen . . . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB help with ExecuteExcel4Macro
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 "cmjat" wrote in message ... 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 a "*.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. -- Jen "Peter T" wrote: 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" wrote in message ... 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! -- Jen "Peter T" wrote: I take it you didn't try it! Regards, Peter T "cmjat" wrote in message ... 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 -- Jen "Peter T" wrote: 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" wrote in message ... 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? --- Jen . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ExecuteExcel4Macro | Excel Programming | |||
Print using ExecuteExcel4Macro | Excel Programming | |||
ExecuteExcel4Macro to run on other page | Excel Programming | |||
ExecuteExcel4Macro ... | Excel Programming | |||
ExecuteExcel4Macro: Help with this | Excel Programming |