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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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
 
Upvote 0
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
 
Upvote 0
Hi,
any idea about my error ExecuteExcel4Macro(arg) = 2023 ?

I have Excel 2002 - SP2.

Thanks.
Giovanni
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

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