Get an Excel File Name from another Excel File

Technical support and scripting issues

Moderators: Dorian (MJT support), JRL

Post Reply
cnfarber
Newbie
Posts: 5
Joined: Thu Aug 02, 2018 7:38 pm

Get an Excel File Name from another Excel File

Post by cnfarber » Thu Apr 04, 2019 2:40 pm

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.

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1348
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: Get an Excel File Name from another Excel File

Post by Dorian (MJT support) » Fri Apr 05, 2019 4:32 pm

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
Yes, we have a Custom Scripting Service. Message me or go here

cnfarber
Newbie
Posts: 5
Joined: Thu Aug 02, 2018 7:38 pm

Re: Get an Excel File Name from another Excel File

Post by cnfarber » Thu Apr 11, 2019 2:40 pm

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


thanks for the help!

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1348
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: Get an Excel File Name from another Excel File

Post by Dorian (MJT support) » Thu Apr 11, 2019 3:38 pm

Aaaah, we've all done it! :D
Yes, we have a Custom Scripting Service. Message me or go here

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts