Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel Macro -- Open specific file?

Status
Not open for further replies.

zincyo

Programmer
Jun 13, 2007
35
US
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!!
 
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
 
Thanks for the help, however i'm looking for code which I can specify the file in my macro, not in the perl script
 
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.
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top