Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
I have a some xlsx files located he J:\Projects\ORF.
Is there a way to extract the 5th row of each file at this location ? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
Workbooks(files) don't have rows.
Each workbook is comprised of one or more worksheets............these worksheets have rows. You must be more specific with your description. But yes..............you can extract data from each of the files(workbooks) in that folder if you know which worksheet contains the required row. Check out Ron de Bruin's site. http://www.rondebruin.nl/tips.htm Browse through the codes you find under his Copy/Paste/Merge Examples. Note the "merge data from all workbooks in a folder" Ron has also supplied an Add-in you could download. Gord Dibben MS Excel MVP On Tue, 21 Jun 2011 12:26:38 -0700 (PDT), carl wrote: I have a some xlsx files located he J:\Projects\ORF. Is there a way to extract the 5th row of each file at this location ? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
On Jun 21, 7:33*pm, Gord Dibben wrote:
Workbooks(files) don't have rows. Each workbook is comprised of one or more worksheets............these worksheets have rows. You must be more specific with your description. But yes..............you can extract data from each of the files(workbooks) in that folder if you know which worksheet contains the required row. Check out Ron de Bruin's site. http://www.rondebruin.nl/tips.htm Browse through the codes you find under his Copy/Paste/Merge Examples. Note the "merge data from all workbooks in a folder" Ron has also supplied an Add-in you could download. Gord Dibben * * MS Excel MVP On Tue, 21 Jun 2011 12:26:38 -0700 (PDT), carl wrote: I have a some xlsx files located he *J:\Projects\ORF. Is there a way to extract the 5th row of each file at this location ?- Hide quoted text - - Show quoted text - Each workbook contains 1 worksheet. I need to extract the 5th row of the worksheet. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
In that case have a look at Ron's code here.
http://www.rondebruin.nl/summary2.htm Gord On Wed, 22 Jun 2011 05:40:30 -0700 (PDT), carl wrote: Each workbook contains 1 worksheet. I need to extract the 5th row of the worksheet. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
On Jun 22, 9:08*am, Gord Dibben wrote:
In that case have a look at Ron's code here. http://www.rondebruin.nl/summary2.htm Gord On Wed, 22 Jun 2011 05:40:30 -0700 (PDT), carl wrote: Each workbook contains 1 worksheet. I need to extract the 5th row of the worksheet.- Hide quoted text - - Show quoted text - Thanks. I looked at my path again. Turns out the workbooks are all located in separate folders. The code you pointed me too looks like I need to select all the workbooks before running it - is that correct ? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
On Jun 22, 9:46*am, carl wrote:
On Jun 22, 9:08*am, Gord Dibben wrote: In that case have a look at Ron's code here. http://www.rondebruin.nl/summary2.htm Gord On Wed, 22 Jun 2011 05:40:30 -0700 (PDT), carl wrote: Each workbook contains 1 worksheet. I need to extract the 5th row of the worksheet.- Hide quoted text - - Show quoted text - Thanks. I looked at my path again. Turns out the workbooks are all located in separate folders. The code you pointed me too looks like I need to select all the workbooks before running it - is that correct ? You would need to fully qualify each folder path for each file. You could, if NOT too many, even use a formula |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
On Jun 22, 1:01*pm, Don Guillett wrote:
On Jun 22, 9:46*am, carl wrote: On Jun 22, 9:08*am, Gord Dibben wrote: In that case have a look at Ron's code here. http://www.rondebruin.nl/summary2.htm Gord On Wed, 22 Jun 2011 05:40:30 -0700 (PDT), carl wrote: Each workbook contains 1 worksheet. I need to extract the 5th row of the worksheet.- Hide quoted text - - Show quoted text - Thanks. I looked at my path again. Turns out the workbooks are all located in separate folders. The code you pointed me too looks like I need to select all the workbooks before running it - is that correct ? You would need to fully qualify each folder path for each file. You could, if NOT too many, even use a formula- Hide quoted text - - Show quoted text - Thanks. Unfortunately, there are 223 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
On Jun 22, 12:14*pm, carl wrote:
On Jun 22, 1:01*pm, Don Guillett wrote: On Jun 22, 9:46*am, carl wrote: On Jun 22, 9:08*am, Gord Dibben wrote: In that case have a look at Ron's code here. http://www.rondebruin.nl/summary2.htm Gord On Wed, 22 Jun 2011 05:40:30 -0700 (PDT), carl wrote: Each workbook contains 1 worksheet. I need to extract the 5th row of the worksheet.- Hide quoted text - - Show quoted text - Thanks. I looked at my path again. Turns out the workbooks are all located in separate folders. The code you pointed me too looks like I need to select all the workbooks before running it - is that correct ? You would need to fully qualify each folder path for each file. You could, if NOT too many, even use a formula- Hide quoted text - - Show quoted text - Thanks. Unfortunately, there are 223 More info perhaps. You have 223 files in ________(how many) folders and do you know the files in each folder and the sheet in the file to get the row???????????????? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
On Jun 22, 2:18*pm, Don Guillett wrote:
On Jun 22, 12:14*pm, carl wrote: On Jun 22, 1:01*pm, Don Guillett wrote: On Jun 22, 9:46*am, carl wrote: On Jun 22, 9:08*am, Gord Dibben wrote: In that case have a look at Ron's code here. http://www.rondebruin.nl/summary2.htm Gord On Wed, 22 Jun 2011 05:40:30 -0700 (PDT), carl wrote: Each workbook contains 1 worksheet. I need to extract the 5th row of the worksheet.- Hide quoted text - - Show quoted text - Thanks. I looked at my path again. Turns out the workbooks are all located in separate folders. The code you pointed me too looks like I need to select all the workbooks before running it - is that correct ? You would need to fully qualify each folder path for each file. You could, if NOT too many, even use a formula- Hide quoted text - - Show quoted text - Thanks. Unfortunately, there are 223 More info perhaps. You have 223 files in ________(how many) folders and do you know the files in each folder and the sheet in the file to get the row????????????????- Hide quoted text - - Show quoted text - Thanks for helping. I have 223 files in 223 folders. Each folder only contains 1 file. All the files have the same name. Each file (workbook) contains 1 sheet named "ORF_Charge". In this sheet, the data I am trying to extract is in row 5. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
I have 223 files in 223 folders. Each folder only contains 1 file. All
the files have the same name. Each file (workbook) contains 1 sheet named "ORF_Charge". In this sheet, the data I am trying to extract is in row 5. I sincerely hope you inherited this storage system from a predecessor. If not, what were you thinking? Did you ever hear of "shared files" where one master workbook is kept up-to-date by multiple users? Can you get a list of all the folders and paths to those folders? Can you get that list into a worksheet in a new workbook? We should be able to use a loop to iterate through that list of path/folders and pull row 5 from each ORF_Change worksheet since all workbooks have the same name. The trick is to get into the 223 folders. Gord On Wed, 22 Jun 2011 12:45:18 -0700 (PDT), carl wrote: |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
On Jun 22, 2:45*pm, carl wrote:
On Jun 22, 2:18*pm, Don Guillett wrote: On Jun 22, 12:14*pm, carl wrote: On Jun 22, 1:01*pm, Don Guillett wrote: On Jun 22, 9:46*am, carl wrote: On Jun 22, 9:08*am, Gord Dibben wrote: In that case have a look at Ron's code here. http://www.rondebruin.nl/summary2.htm Gord On Wed, 22 Jun 2011 05:40:30 -0700 (PDT), carl wrote: Each workbook contains 1 worksheet. I need to extract the 5th row of the worksheet.- Hide quoted text - - Show quoted text - Thanks. I looked at my path again. Turns out the workbooks are all located in separate folders. The code you pointed me too looks like I need to select all the workbooks before running it - is that correct ? You would need to fully qualify each folder path for each file. You could, if NOT too many, even use a formula- Hide quoted text - - Show quoted text - Thanks. Unfortunately, there are 223 More info perhaps. You have 223 files in ________(how many) folders and do you know the files in each folder and the sheet in the file to get the row????????????????- Hide quoted text - - Show quoted text - Thanks for helping. I have 223 files in 223 folders. Each folder only contains 1 file. All the files have the same name. Each file (workbook) contains 1 sheet named "ORF_Charge". In this sheet, the data I am trying to extract is in row 5. Might I also suggest that you rename the files to include an identifier such as the folder name myfilefolder.xls or similar. Then put all in ONE folder and then loop thru all files in that folder. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
On Jun 22, 5:16*pm, Gord Dibben wrote:
I have 223 files in 223 folders. Each folder only contains 1 file. All the files have the same name. Each file (workbook) contains 1 sheet named "ORF_Charge". In this sheet, the data I am trying to extract is in row 5. I sincerely hope you inherited this storage system from a predecessor. If not, what were you thinking? Did you ever hear of "shared files" where one master workbook is kept up-to-date by multiple users? Can you get a list of all the folders and paths to those folders? Can you get that list into a worksheet in a new workbook? We should be able to use a loop to iterate through that list of path/folders and pull row 5 from each ORF_Change worksheet since all workbooks have the same name. The trick is to get into the 223 folders. Gord On Wed, 22 Jun 2011 12:45:18 -0700 (PDT), carl wrote:- Hide quoted text - - Show quoted text - Thanks again. This file structure was a result of decompressing. I used winzip to do the decompression. So if I can get the file path details into a spreadsheet we might be able to do the extract - J: \Projects\ORF\Meeds\xlsx\005\ORF.xlsx ? |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
Don's suggestions could make it easier but changing the names of 223 files in
223 folders may be an onerous task. If you could get J:\Projects\ORF\Meeds\xlsx\005\ and all other paths into a list a loop through could be done although getting the list may also be onerous. After all, the workbook name............ORF.xls...........and the worksheet ORF_Change do not vary from folder to folder so what the code would do is loop through the path names pulling data from each workbook into a sheet in your Master workbook Gord On Thu, 23 Jun 2011 06:39:03 -0700 (PDT), carl wrote: Thanks again. This file structure was a result of decompressing. I used winzip to do the decompression. So if I can get the file path details into a spreadsheet we might be able to do the extract - J: \Projects\ORF\Meeds\xlsx\005\ORF.xlsx ? |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
On Jun 23, 10:57*am, Gord Dibben wrote:
Don's suggestions could make it easier but changing the names of 223 files in 223 folders may be an onerous task. If you could get J:\Projects\ORF\Meeds\xlsx\005\ *and all other paths into a list a loop through could be done although getting the list may also be onerous. After all, the workbook name............ORF.xls...........and the worksheet ORF_Change do not vary from folder to folder so what the code would do is loop through the path names pulling data from each workbook into a sheet in your Master workbook Gord On Thu, 23 Jun 2011 06:39:03 -0700 (PDT), carl wrote: Thanks again. This file structure was a result *of decompressing. I used winzip to do the decompression. So if I can get the file path details into a spreadsheet we might be able to do the extract - J: \Projects\ORF\Meeds\xlsx\005\ORF.xlsx ?- Hide quoted text - - Show quoted text - Ok. I have the list of paths in excel. So with that list in A1:A223, can you show me the code that will go into each of these files and extract the 5 row of data ? |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
I will have to work on it...............not as adept at VBA as many of the
others here. All folders contain a workbook named ORF.xls All ORF workbooks contain a sheet named ORF_Change We want row 5 from each sheet to accumulate in a new sheet in a workbook. The code would reside in only that one workbook. The trick for me is to loop through A1:A223 to get the changing paths and use Ron's code to pull from a closed workbook. http://www.rondebruin.nl/copy7.htm Help! anybody. Gord On Thu, 23 Jun 2011 10:10:43 -0700 (PDT), carl wrote: Ok. I have the list of paths in excel. So with that list in A1:A223, can you show me the code that will go into each of these files and extract the 5 row of data ? |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
hi carl,
i suppose column "A" contains data like "J:\Projects\ORF\Meeds\xlsx\005\ORF.xlsx" you have to add reference Microsoft ActiveX Data Objects 2.8 if "Microsoft.ACE.OLEDB.10.0" is not installed on your PC, just tell us, there are many others possibilities. Sub test() For i = 1 To 223 ReadFile Range("A" & i), "ORF_Charge", "A5:L5" 'adapt range Next End Sub Function ReadFile(Fichier As String, Sh As String, Rgn As String) Dim Source As ADODB.Connection Dim Donnees As Variant Dim Rst As ADODB.Recordset Set Source = New ADODB.Connection With Source .Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionString = "Provider=Microsoft.ACE.OLEDB.10.0;Data Source=" _ & Fichier & ";Extended Properties=""Excel 10.0;HDR=YES;""" .Open End With Donnees = "SELECT * FROM [" & Sh & "$" & Rgn & "]" Set Rst = New ADODB.Recordset Set Rst = Source.Execute(Donnees) Sheets(2).Range("A" & i).CopyFromRecordset Rst 'adapt sheet name or index Source.Close Set Source = Nothing End Function -- isabelle |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
correction,
xl2002 -- Excel 10.0 xl2007 -- Excel 12.0 -- isabelle |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
Thanks for jumping into this Isabelle.
My plea for help has garnered some interest<g Gord On Thu, 23 Jun 2011 22:56:36 -0400, isabelle wrote: hi carl, i suppose column "A" contains data like "J:\Projects\ORF\Meeds\xlsx\005\ORF.xlsx" you have to add reference Microsoft ActiveX Data Objects 2.8 if "Microsoft.ACE.OLEDB.10.0" is not installed on your PC, just tell us, there are many others possibilities. Sub test() For i = 1 To 223 ReadFile Range("A" & i), "ORF_Charge", "A5:L5" 'adapt range Next End Sub Function ReadFile(Fichier As String, Sh As String, Rgn As String) Dim Source As ADODB.Connection Dim Donnees As Variant Dim Rst As ADODB.Recordset Set Source = New ADODB.Connection With Source .Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionString = "Provider=Microsoft.ACE.OLEDB.10.0;Data Source=" _ & Fichier & ";Extended Properties=""Excel 10.0;HDR=YES;""" .Open End With Donnees = "SELECT * FROM [" & Sh & "$" & Rgn & "]" Set Rst = New ADODB.Recordset Set Rst = Source.Execute(Donnees) Sheets(2).Range("A" & i).CopyFromRecordset Rst 'adapt sheet name or index Source.Close Set Source = Nothing End Function |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
i Gord,
you understand [g] right Gord, it's with pleasure ;-) i just remember that this can be done much more simply with old tools "Excel4" to read a single row is more than enough i know it's ok on XL2002 to 2007 but i do not know for xl2010 Sub test2() Dim nRow As Integer, nColumn As Integer, n As Integer Dim sDir As String nRow = 5 For i = 1 To 1 sDir = Range("A" & i) 'Range("A1:Ax") contains values like "c:\temp\" n = n + 1 For nColumn = 1 To 256 Sheets(2).Cells(n, nColumn) = ExecuteExcel4Macro _ ("'" & sDir & "[MyFile.xls]ORF_Charge'!R" & nRow & "C" & nColumn & "") Next Next End Sub -- isabelle |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
sorry my fingers are tired a "h" is missing
-- isabelle |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
On Jun 24, 12:25*am, isabelle wrote:
sorry my fingers are tired a "h" is missing -- isabelle Thanks you Isabelle. I am using Excel2003 SP1 (11.6355.6360) I tried to use this one (of the 2 your offerred) Sub test2() Dim nRow As Integer, nColumn As Integer, n As Integer Dim sDir As String nRow = 5 For i = 1 To 1 sDir = Range("A" & i) 'Range("A1:Ax") contains values like "c:\temp \" n = n + 1 For nColumn = 1 To 256 Sheets(2).Cells(n, nColumn) = ExecuteExcel4Macro _ ("'" & sDir & "[MyFile.xls]ORF_Charge'!R" & nRow & "C" & nColumn & "") Next Next End Sub The macro would prompt a window called "MyFile.xls" - it looked like an explorer window. What am I suppose to do at this point ? Thanks again. |
#22
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
Carl
MyFile.xls should be ORF.xls Here is corrected code which works for me in a test on three folders listed in A1:A3 of Sheet1 of a new workbook...............code is in a module of that new workbook. A1............."C:\Gordstuff" A2............."C"\Mystuff" A3............."C:\Yourstuff" Sub test2() Dim nRow As Integer, nColumn As Integer, n As Integer Dim sDir As String nRow = 5 For i = 1 To 3 'where 3 is the range of paths in Sheet1 A1:A3 'adjust as necessary sDir = Range("A" & i) n = n + 1 For nColumn = 1 To 25 Sheets(2).Cells(n, nColumn) = ExecuteExcel4Macro _ ("'" & sDir & "[ORF.xls]ORF_Charge'!R" & nRow & "C" & nColumn & "") Next Next End Sub Gord On Mon, 27 Jun 2011 09:42:59 -0700 (PDT), carl wrote: On Jun 24, 12:25*am, isabelle wrote: sorry my fingers are tired a "h" is missing -- isabelle Thanks you Isabelle. I am using Excel2003 SP1 (11.6355.6360) I tried to use this one (of the 2 your offerred) Sub test2() Dim nRow As Integer, nColumn As Integer, n As Integer Dim sDir As String nRow = 5 For i = 1 To 1 sDir = Range("A" & i) 'Range("A1:Ax") contains values like "c:\temp \" n = n + 1 For nColumn = 1 To 256 Sheets(2).Cells(n, nColumn) = ExecuteExcel4Macro _ ("'" & sDir & "[MyFile.xls]ORF_Charge'!R" & nRow & "C" & nColumn & "") Next Next End Sub The macro would prompt a window called "MyFile.xls" - it looked like an explorer window. What am I suppose to do at this point ? Thanks again. |
#23
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
On Jun 24, 12:25*am, isabelle wrote:
sorry my fingers are tired a "h" is missing -- isabelle Thank you Isabella. I am running Excel 2003 (version 11.6355.6360) SP1. I tried this suggestion from above: Sub test2() Dim nRow As Integer, nColumn As Integer, n As Integer Dim sDir As String nRow = 5 For i = 1 To 1 sDir = Range("A" & i) 'Range("A1:Ax") contains values like "c:\temp \" n = n + 1 For nColumn = 1 To 256 Sheets(2).Cells(n, nColumn) = ExecuteExcel4Macro _ ("'" & sDir & "[MyFile.xls]ORF_Charge'!R" & nRow & "C" & nColumn & "") Next Next End Sub The macro prompts me with an explorer type window titled MyFile.xls and looks like it is asking me to select a file. Not sure what I am suppose to be doing at this prompt. Thanks again for your help. |
#24
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
hi carl,
i hope that the correction made €‹€‹by Gord has solved your problem, -- isabelle |
#25
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
I hope so Isabelle.
Actually wasn't a correction to your code which ran fine for me as noted. Looks like Carl copied your code and did not realise he had to change MyFile.xls to ORF.xls The macro would prompt a window called "MyFile.xls" - it looked like an explorer window. I would like 10 cents for every time I was testing code and forgot something like the above. Scratching head<g Gord On Mon, 27 Jun 2011 17:17:20 -0400, isabelle wrote: hi carl, i hope that the correction made ??by Gord has solved your problem, |
#26
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
everything's fine !
-- isabelle |
#27
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
On Jun 24, 12:19*am, isabelle wrote:
i Gord, you understand [g] right Gord, it's with pleasure ;-) i just remember that this can be done much more simply with old tools "Excel4" to read a single row is more than enough i know it's ok on XL2002 to 2007 but i do not know for xl2010 Sub test2() Dim nRow As Integer, nColumn As Integer, n As Integer Dim sDir As String nRow = 5 For i = 1 To 1 * *sDir = Range("A" & i) 'Range("A1:Ax") contains values like "c:\temp\" * *n = n + 1 * *For nColumn = 1 To 256 * * *Sheets(2).Cells(n, nColumn) = ExecuteExcel4Macro _ * * *("'" & sDir & "[MyFile.xls]ORF_Charge'!R" & nRow & "C" & nColumn & "") * *Next Next End Sub -- isabelle I tried using this one: Sub test2() Dim nRow As Integer, nColumn As Integer, n As Integer Dim sDir As String nRow = 5 For i = 1 To 1 sDir = Range("A" & i) 'Range("A1:Ax") contains values like "c:\temp\" n = n + 1 For nColumn = 1 To 256 Sheets(2).Cells(n, nColumn) = ExecuteExcel4Macro _ ("'" & sDir & "[MyFile.xls]ORF_Charge'!R" & nRow & "C" & nColumn & "") Next Next End Sub But was prompted with an explorer type window named "MyFile" - not sure what to do at this point. I am running excel 2003. |
#28
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
|
#29
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
On Jun 27, 2:36*pm, Gord Dibben wrote:
Carl MyFile.xls * should be *ORF.xls Here is corrected code which works for me in a test on three folders listed in A1:A3 of Sheet1 of a new workbook...............code is in a module of that new workbook. A1............."C:\Gordstuff" A2............."C"\Mystuff" A3............."C:\Yourstuff" Sub test2() Dim nRow As Integer, nColumn As Integer, n As Integer Dim sDir As String nRow = 5 For i = 1 To 3 *'where 3 is the range of paths in Sheet1 A1:A3 'adjust as necessary * *sDir = Range("A" & i) * *n = n + 1 * *For nColumn = 1 To 25 * * *Sheets(2).Cells(n, nColumn) = ExecuteExcel4Macro _ * * *("'" & sDir & "[ORF.xls]ORF_Charge'!R" & nRow & "C" & nColumn & "") * *Next Next End Sub Gord On Mon, 27 Jun 2011 09:42:59 -0700 (PDT), carl wrote: On Jun 24, 12:25*am, isabelle wrote: sorry my fingers are tired a "h" is missing -- isabelle Thanks you Isabelle. I am using Excel2003 SP1 (11.6355.6360) I tried to use this one (of the 2 your offerred) Sub test2() Dim nRow As Integer, nColumn As Integer, n As Integer Dim sDir As String nRow = 5 For i = 1 To 1 * sDir = Range("A" & i) 'Range("A1:Ax") contains values like "c:\temp \" * n = n + 1 * For nColumn = 1 To 256 * * Sheets(2).Cells(n, nColumn) = ExecuteExcel4Macro _ * * ("'" & sDir & "[MyFile.xls]ORF_Charge'!R" & nRow & "C" & nColumn & "") * Next Next End Sub The macro would prompt a window called "MyFile.xls" - it looked like an explorer window. What am I suppose to do at this point ? Thanks again.- Hide quoted text - - Show quoted text - Thanks Gord. I made the changes but the macro still prompts me with the explorer window to select a file. I have my list of paths in Sheet 1 A1:A3 Here's the code I am using. Sub test2() Dim nRow As Integer, nColumn As Integer, n As Integer Dim sDir As String nRow = 5 For i = 1 To 3 'where 3 is the range of paths in Sheet1 A1:A3 'adjust as necessary sDir = Range("A" & i) n = n + 1 For nColumn = 1 To 5 Sheets(2).Cells(n, nColumn) = ExecuteExcel4Macro _ ("'" & sDir & "[ORF.xlsx]ORF_Charge'!R" & nRow & "C" & nColumn & "") Next Next End Sub The only change I made was the file extension "xls" to "xlsx" and nColumn set to 5. |
#30
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
is that list contains values like "c:\temp\" with a backslash in the end ?
-- isabelle |
#31
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
On Jun 27, 5:17Â*pm, isabelle wrote:
hi carl, i hope that the correction made €‹€‹by Gord has solved your problem, -- isabelle Thanks. I did try this: Sub test2() Dim nRow As Integer, nColumn As Integer, n As Integer Dim sDir As String nRow = 5 For i = 1 To 3 'where 3 is the range of paths in Sheet1 A1:A3 'adjust as necessary sDir = Range("A" & i) n = n + 1 For nColumn = 1 To 5 Sheets(2).Cells(n, nColumn) = ExecuteExcel4Macro _ ("'" & sDir & "[ORF.xlsx]ORF_Charge'!R" & nRow & "C" & nColumn & "") Next Next End Sub So I've changed the file extension to xlsx. My file paths is in sheet 1 A1:A3. Like this J:\Projects\ORF\Meeds\xlsx \005\ORF.xls When I run the macro I still get prompted with an explorer window that look like I need to select a file. Tha window is pointed to my local pc desktop. |
#32
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
I don't know what else to say
Are the paths in A1:A3 in double quotes? "C:\the path\more path\rest of path" Does the workbook ORF.xlsx exist in each of the tree folders? I cannot replicate the "explorer window" unless one or both of the above criteria are not met. Gord On Thu, 30 Jun 2011 08:48:22 -0700 (PDT), carl wrote: Thanks Gord. I made the changes but the macro still prompts me with the explorer window to select a file. I have my list of paths in Sheet 1 A1:A3 Here's the code I am using. Sub test2() Dim nRow As Integer, nColumn As Integer, n As Integer Dim sDir As String nRow = 5 For i = 1 To 3 'where 3 is the range of paths in Sheet1 A1:A3 'adjust as necessary sDir = Range("A" & i) n = n + 1 For nColumn = 1 To 5 Sheets(2).Cells(n, nColumn) = ExecuteExcel4Macro _ ("'" & sDir & "[ORF.xlsx]ORF_Charge'!R" & nRow & "C" & nColumn & "") Next Next End Sub The only change I made was the file extension "xls" to "xlsx" and nColumn set to 5. |
#33
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
You have neglected to enclose the full paths in double quotes as you were shown
6/27/2011 posting by me. See my earlier posting today. Gord On Thu, 30 Jun 2011 10:34:03 -0700 (PDT), carl wrote: On Jun 27, 5:17*pm, isabelle wrote: hi carl, i hope that the correction made ??by Gord has solved your problem, -- isabelle Thanks. I did try this: Sub test2() Dim nRow As Integer, nColumn As Integer, n As Integer Dim sDir As String nRow = 5 For i = 1 To 3 'where 3 is the range of paths in Sheet1 A1:A3 'adjust as necessary sDir = Range("A" & i) n = n + 1 For nColumn = 1 To 5 Sheets(2).Cells(n, nColumn) = ExecuteExcel4Macro _ ("'" & sDir & "[ORF.xlsx]ORF_Charge'!R" & nRow & "C" & nColumn & "") Next Next End Sub So I've changed the file extension to xlsx. My file paths is in sheet 1 A1:A3. Like this J:\Projects\ORF\Meeds\xlsx \005\ORF.xls When I run the macro I still get prompted with an explorer window that look like I need to select a file. Tha window is pointed to my local pc desktop. |
#34
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
Isabelle
It doesn't matter whether the backslash is present or not. Works either way for me.............what is critical are the double quotes. Gord On Thu, 30 Jun 2011 13:16:06 -0400, isabelle wrote: is that list contains values like "c:\temp\" with a backslash in the end ? |
#35
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
I meant backslash "at the end"
On Thu, 30 Jun 2011 11:21:16 -0700, Gord Dibben wrote: Isabelle It doesn't matter whether the backslash is present or not. Works either way for me.............what is critical are the double quotes. Gord On Thu, 30 Jun 2011 13:16:06 -0400, isabelle wrote: is that list contains values like "c:\temp\" with a backslash in the end ? |
#36
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
On Jun 30, 2:23*pm, Gord Dibben wrote:
I meant backslash "at the end" On Thu, 30 Jun 2011 11:21:16 -0700, Gord Dibben wrote: Isabelle It doesn't matter whether the backslash is present or not. Works either way for me.............what is critical are the double quotes. Gord On Thu, 30 Jun 2011 13:16:06 -0400, isabelle wrote: is that list contains values like "c:\temp\" with a backslash in the end ?- Hide quoted text - - Show quoted text - Thanks all. I've worked through this intital issue. Now the results display #REF1. Can you confirm that this will work on an XLSX file - I am running Excel 2003. |
#37
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VBA Question
OK! At least we've worked through the "explorer window".
In your workbook with the code you have two sheets. Sheet1 and Sheet2 Code is in a General Module. A1:A3 of Sheet1 has the paths. Select Sheet2 and run the macro. I don't have 2007 installed at present but I don't know why the code should not work in 2007 version with 2007 version workbooks. It won't work if all you have is 2003 and are trying to communicate with 2007 ORF.xlsx workbooks. Gord On Fri, 1 Jul 2011 12:25:28 -0700 (PDT), carl wrote: Thanks all. I've worked through this intital issue. Now the results display #REF1. Can you confirm that this will work on an XLSX file - I am running Excel 2003. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Macro/VB Question DDE Question | Excel Worksheet Functions | |||
SUM question | Excel Worksheet Functions | |||
where can I see my question and answer? Yesterday I ask a question | Excel Discussion (Misc queries) | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
The question is an excel question that I need to figure out howto do in excel. | Excel Worksheet Functions |