VBA Question

  • Thread starter Thread starter carl
  • Start date Start date
sorry my fingers are tired a "h" is missing
Click to expand...

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.
 
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

sorry my fingers are tired a "h" is missing
Click to expand...

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.
Click to expand...
 
sorry my fingers are tired a "h" is missing
Click to expand...

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.
 
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.
Click to expand...

I would like 10 cents for every time I was testing code and forgot something
like the above.

Scratching head<g>


Gord
 
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
Click to expand...

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
Click to expand...

But was prompted with an explorer type window named "MyFile" - not
sure what to do at this point.

I am running excel 2003.
 
Carl

You're missing a post or not reading the ones being sent.

I posted this yesterday.....................

(e-mail address removed)


Gord

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
Click to expand...

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
Click to expand...

But was prompted with an explorer type window named "MyFile" - not
sure what to do at this point.

I am running excel 2003.
Click to expand...
 
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



Thanks you Isabelle.
Click to expand...
I am using Excel2003 SP1 (11.6355.6360)
Click to expand...
I tried to use this one (of the 2 your offerred)
Click to expand...
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
Click to expand...
The macro would prompt a window called "MyFile.xls" - it looked like
an explorer window.
Click to expand...
What am I suppose to do at this point ?
Click to expand...
Thanks again.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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.
 
hi carl,

i hope that the correction made ​​by Gord has solved yourproblem,
Click to expand...

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.
 
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
 
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


hi carl,

i hope that the correction made ??by Gord has solved your problem,
Click to expand...

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.
Click to expand...
 
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
 
I meant backslash "at the end"

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
Click to expand...
 
I meant backslash "at the end"







- Show quoted text -
Click to expand...

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.
 
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
 
Back
Top