Ottsel
Board Regular
- Joined
- Jun 4, 2022
- Messages
- 167
- Office Version
- 365
- Platform
- 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!
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!