VB help with ExecuteExcel4Macro
in [Programming]
|
From: Peter T on 6 Apr 2010 07:31 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" <cmjat(a)discussions.microsoft.com> wrote in message news:A32E9FE9-8AC8-4C72-8A85-26F659734996(a)microsoft.com... > 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" <cmjat(a)discussions.microsoft.com> wrote in message >> news:ECA8DE98-F2F7-491A-82E3-9B4EB8889121(a)microsoft.com... >> > 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" <cmjat(a)discussions.microsoft.com> wrote in message >> >> news:A0540903-185F-42B6-96B1-A3B156AC3AC8(a)microsoft.com... >> >> > 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 >> >> >> >> >> >> . >> >> >> >> >> . >>
From: cmjat on 9 Apr 2010 15:55 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. -- 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" <cmjat(a)discussions.microsoft.com> wrote in message > news:A32E9FE9-8AC8-4C72-8A85-26F659734996(a)microsoft.com... > > 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" <cmjat(a)discussions.microsoft.com> wrote in message > >> news:ECA8DE98-F2F7-491A-82E3-9B4EB8889121(a)microsoft.com... > >> > 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" <cmjat(a)discussions.microsoft.com> wrote in message > >> >> news:A0540903-185F-42B6-96B1-A3B156AC3AC8(a)microsoft.com... > >> >> > 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 > >> >> > >> >> > >> >> . > >> >> > >> > >> > >> . > >> > > > . >
From: Peter T on 11 Apr 2010 07:43 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" <cmjat(a)discussions.microsoft.com> wrote in message news:AB96AFEF-D358-4643-94EA-31DA038E4416(a)microsoft.com... > 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. > -- > 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" <cmjat(a)discussions.microsoft.com> wrote in message >> news:A32E9FE9-8AC8-4C72-8A85-26F659734996(a)microsoft.com... >> > 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" <cmjat(a)discussions.microsoft.com> wrote in message >> >> news:ECA8DE98-F2F7-491A-82E3-9B4EB8889121(a)microsoft.com... >> >> > 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" <cmjat(a)discussions.microsoft.com> wrote in message >> >> >> news:A0540903-185F-42B6-96B1-A3B156AC3AC8(a)microsoft.com... >> >> >> > 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 >> >> >> >> >> >> >> >> >> . >> >> >> >> >> >> >> >> >> . >> >> >> >> >> . >>
First
|
Prev
|
Pages: 1 2 Prev: DoubleClick to return SeriesIndex & PointIndex Next: File name being changed |