VBA Open Sequence of Files Depending on Cell Context for Automation

Ottsel

Board Regular
Joined
Jun 4, 2022
Messages
101
Office Version
  1. 365
Platform
  1. Windows
I'm still in the beginning process of a code that'll open a sequence of other excel files, so I can extract certain content from them, but I haven't quite figured out how to begin the process.

Workbooks.Open "G:\Jobs '\...\....\....
Normally if I declare a certain pathway it'll always refer to a certain workbook or document depending on where I've stored it, which is simple, but my workbooks placement change depending on what someone is seeking.

The file system is easy to predict. Within "Jobs" in the G drive there is the customer folder, which is referenced in G1. Following that is the Project folder, which is in A1 and all projects are labeled "Project" following the order number and ending in ".xls".

Example: "ALD10105.xls"

The rough draft that I've encountered an issue with, as I know its not structured correctly:

Dim cCustomer As String 'customer
Dim cProject As String 'project

cCustomer = ActiveSheet.Range("A1").Value
cProject = ActiveSheet.Range("G1").Value

Workbooks.Open "G:\JOBCARDS\(bCode)\(jTract)\Lot&"

I'm not fully sure how to approach this, so any tips, tricks or other posts that I was unsuccessful in locating prior to this posting would be greatly appreciated.

Currently, I am able to extract all the data when I manually open all the documents and run that section of the code, but was trying to go with a more automatic approach.

As mentioned; A1 contains the Project, G1 contains the customer.
A2 and onward contain the order number, which then the data can be extracted. Sometimes there are only 3 order numbers, so A2:A4, but other times there can be 25, so A2:A26. This is why I was hoping to automate the approach and make it less laggy. Perhaps opening one project, extract the data, then close and proceed onto the next file. I could see a for loop being used to gather the order numbers to be used in file pathway, but not certain.

Mostly searching for ideas, as there might be an approach that is 100% times easier that I'm not seeing.

Thanks!
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Forum statistics

Threads
1,176,671
Messages
5,904,384
Members
435,089
Latest member
blackstapler

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top