Get an Excel File Name from another Excel File

[cnfarber]

Starting up a big project. My goal is to open multiple excel files, look for lines that are "past due" and enter the values as a summary into another excel sheet or word document. I want to have a master file of the names of the excel sheets. The script will use this master sheet and open up the other excel files one at a time.

This is where I ran into my first roadblock:

Let>filename=C:\Users\USER\Desktop\masterfile.xlsx
XLOpen>filename,1,xlH
XLGetCell>xlH,Sheet1,row,1,field_1

Let>filename=C:\Users\USER\Desktop\%field_1%
XLOpen>filename,1,xlH

MS seems to want the file name typed out rather than having %field_1% as part of the file name. Any ideas on how to open an excel file from another excel file or general tips for what I am looking to accomplish? I can get past this step by hard coding in the file names but since they will be changing over time, it's not ideal.

[Dorian (MJT support)]

Initially I thought it might be because you were using xlH twice, but i tried that too and it didn't seem to cause a problem. I've changed the second xlH to xlH22 anyway.

So my guess is that either the variable "row" doesn't exist, or if it does, the filename is not in the cell it's pointing to. I just tried this, and it works. I've commented out your paths for mine.

Code: Select all

//Let>filename=C:\Users\USER\Desktop\masterfile.xlsx
Let>filename=d:\MJT\files\Excel\FileNames.xlsx
XLOpen>filename,1,xlH
XLGetCell>xlH,Sheet1,1,1,field_1

//Let>filename=C:\Users\USER\Desktop\%field_1%
Let>filename=d:\MJT\files\Excel\%field_1%
XLOpen>filename,1,xlH2

[cnfarber]

row didn't exist... should have noticed that myself


thanks for the help!

[Dorian (MJT support)]

Aaaah, we've all done it!