PDA

View Full Version : Trouble Getting ?ExecuteExcel4Macro? to Work



Cyberdude
03-21-2006, 04:03 PM
I have been using ?ExecuteExcel4Macro? in many macros for over a year now, and I?ve never had a problem getting it to work until today. I?m using it to fetch data from some ?.csv? files, so I have to use ?R1C1? to specify the source address. I?m having trouble using statements like the following:

Const Path$ = "'C:\EXCEL Documents\[FO Data.csv]FO Data'!"
For N = 1 To 20
Range("M" & N + 3).Value = ExecuteExcel4Macro(Path & Cells(N, 1).Address(ReferenceStyle:=xlR1C1))
Next N
These statements DO work when conditions are right. Normally with ?ExecuteExcel4Macro? applications I never open the file that I?m fetching data from, but when using the ?R1C1? technique to specify the source address, I discovered that I MUST have the source .csv file open. So I added statements to open the .csv file, then close it at the end of the macro. The file DOES get opened, and later closed, but no data is fetched. Keep in mind that when I open the file manually before runniing the macro, IT WORKS!

I actually process four .csv files in one macro, and they all behave the same way. I can read data from all four files IF I have them open when the macro begins, but not when the macro opens them. It almost seems like maybe there is an asynchronous operation going on here, so that the macro continues to execute at the same time the .csv files are being opened, and by the time the files are opened, the macro statements reading the files have completed execution.

I might add that in my other programs in which I use ?ExecuteExcel4Macro? successfully I specify the source address by defning names for each cell in the source workbooks, so the address specification consists of the path concatenated with the defined name. I don?t use Cells(N, 1).Address(ReferenceStyle:=xlR1C1) in those macros. The other difference is that my successful applications have Excel workbooks as the data source. I?m currently using .csv files when I have the problem.

Any ideas on how I can avoid opening the files in order to use them?