Different solution - not open the workbooks.

gio123bg

Active Member
Joined
Feb 14, 2004
Messages
255
Hi to all.
My following macro works correcly but I need a different solution without the necessity to open the workbook.
...
Do While MyFile <> ""
Application.StatusBar = MyFile
Workbooks.Open Filename:=dirfile & MyFile
Sheet1.Cells(i, 1) = ActiveWorkbook.Sheets(15).Range("B3").Value
Sheet1.Cells(i, 2) = ActiveWorkbook.Sheets(15).Range("B4").Value
MyValue = ActiveWorkbook.Sheets(15).Range("C20").Value
' ThisWorkbook.Sheets(1).Range("C20").Value = MyValue
Sheet1.Cells(i, 3) = MyValue
For k = 1 To 14
Sheet1.Cells(i, k + 3) = ActiveWorkbook.Sheets(15).Cells(k + 5, 3).Value
Next
'MyValue = MyValue + ActiveWorkbook.Sheets(1).Range("H2").Value
ActiveWorkbook.Close savechanges:=False
j = j + 1
dirfile = RootDir & myArray(j) & "\"
MyFile = Dir(dirfile & "*.xls")
i = i + 1
Loop

Any idea?
Thanks in advance.
Regards.

Giovanni
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

gio123bg

Active Member
Joined
Feb 14, 2004
Messages
255
Ok, thanks.
I try to run my new macro but I receive un error.

More exacly, before calling the function, I need to receive the B3 cell of the remote sheet of the closed workbook. Debugging I find all values passed to the funcion but I suppose that:

arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)


must be changed because "Ref"=$B$3 but "arg"=.....Sheetsxx!R3C2

Do you have a suggestion for me?
Thanks.

Giovanni
 

gio123bg

Active Member
Joined
Feb 14, 2004
Messages
255
Hi,
I receive an error for "xlR1C1"=-4150 ; "arg" is ok and ExecuteExcel4Macro(arg) = 2023 at the first run of the function.

My you hemp me? Thanks.

Giovanni
 

gio123bg

Active Member
Joined
Feb 14, 2004
Messages
255

ADVERTISEMENT

Hi,
any idea about my error ExecuteExcel4Macro(arg) = 2023 ?

I have Excel 2002 - SP2.

Thanks.
Giovanni
 

MartinK

Active Member
Joined
Oct 30, 2003
Messages
384
ExecuteExcel4Macro(arg) = 2023
hello again,
some of your arguments was not valid or sheet not found.
B3 converted to R3C2 is OK, that's the R1C1 notation this function works with. This is not causing the error.

What do you pass to the four arguments? Especially check the "path" ("p") argument.
 

gio123bg

Active Member
Joined
Feb 14, 2004
Messages
255
Ok! Thanks!
The problem was the wrong setting of the sheet name. In my case, Sheet14(Account). I was set s="Sheet14". The correct setting is s="Account", the name of the sheet.

Giovanni
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,332
Messages
5,769,470
Members
425,552
Latest member
learnerrr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top