Excel Macro -- Open specific file?

[zincyo]

Hey,

I have written a rather long macro for excel which will daily bring in our recon file and then use it to refresh several pivot tables. My macro automatically runs through a perl script, however once the refresh begins in excel, I must manually select the files. I was wondering if there is a macro i can write which will, for example, automatically refresh the pivot table with a file named "refresh.txt" in the same directory as the workbook (meaning I don't want to have to write c:\documents and settings... etc, but rather just current directory\refresh.txt

thanks for the help!!

 

[paulpayne]

I think you could use this.

sub currentdirectory()
dim strCD as string
dim strFilename

strCD = ActiveWorkbook.Path
strFilename = strCD & "\Refresh.txt"

....rest of code here....

end sub

 

[zincyo]

Thanks for the help, however i'm looking for code which I can specify the file in my macro, not in the perl script

 

[zincyo]

Hey, I actually did it the way you suggested, which I realized works, HOWEVER:

I get run-time error 1004

"Excel cannot find the text file to refresh this external data range.

 

[paulpayne]

If you put a debug.print line in after the strFilename line, this will give you the text of the file you are trying to find, maybe the text is not quite right. i.e.

c:\datarefresh.txt instead of c:\data\refresh.txt