VBA Question

C

carl

sorry my fingers are tired a "h" is missing

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

Gord Dibben

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

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

carl

sorry my fingers are tired a "h" is missing

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

isabelle

hi carl,

i hope that the correction made ​​by Gord has solved your problem,
 
G

Gord Dibben

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
 
C

carl

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

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

Gord Dibben

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

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

carl

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

carl

hi carl,

i hope that the correction made ​​by Gord has solved yourproblem,

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

Gord Dibben

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
 
G

Gord Dibben

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,

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

Gord Dibben

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
 
G

Gord Dibben

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
 
C

carl

I meant backslash "at the end"







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

Gord Dibben

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top