VBA Question

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

 

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

 

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

 

[isabelle]

hi carl,

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

 

[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

 

[isabelle]

everything's fine !

 

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

 

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

 

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

 

[isabelle]

is that list contains values like "c:\temp\" with a backslash in the end ?

 

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

 

[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

 

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

 

[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

 

[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

 

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

 

[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