Home 
Search 
Today's Posts 
#21




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




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




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




VBA Question
hi carl,
i hope that the correction made â€‹â€‹by Gord has solved your problem,  isabelle 
#25




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




VBA Question
everything's fine !
 isabelle 
#27




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




VBA Question

#29




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




VBA Question
is that list contains values like "c:\temp\" with a backslash in the end ?
 isabelle 
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 