Hello, I would like to preface this with many thanks for insight or VBA sharing to assist in resolving my issue.
Background, I am utilizing Excel PQ to import multiple files from a directory, scrub the files, and load the massaged results files into a single worksheet named “Data” and table named “tbl_Load".
Column header is in row 1 and moves from Column A – Column D . Column C (Store Number) contains a listing of locations that I would like to use in a VBA script to save each new instance into a new workbook, using the value from this filtered field in the filename.
Example (reduced for simplicity). Below Data would be post PQ import/refresh. What I would like to do is run a script that will save a new file containing all records at the unique store number level So, in this data set, we see rows 2:8 are associated with Store 1, rows 9:15 are associated with records from store 5 and row 16 is associated with records for store 50 (stores could have varying record numbers).
Desired output would be 3 new .csv files that will output to a directory: \\Share\Load\Import with the store number as worksheet name along with store name and date appended as the filename. So, using example above post script would generate 3 unique files:
Store_1_08212023.csv (would contain 8 rows of records including header)
Store_5_08212023.csv (would contain 8 rows of records including header)
Store_50_0821203.csv (would contain 2 rows of records including header)
Anyone have a script to share that could perform such actions? Thanks again for any assistance!
Background, I am utilizing Excel PQ to import multiple files from a directory, scrub the files, and load the massaged results files into a single worksheet named “Data” and table named “tbl_Load".
Column header is in row 1 and moves from Column A – Column D . Column C (Store Number) contains a listing of locations that I would like to use in a VBA script to save each new instance into a new workbook, using the value from this filtered field in the filename.
Example (reduced for simplicity). Below Data would be post PQ import/refresh. What I would like to do is run a script that will save a new file containing all records at the unique store number level So, in this data set, we see rows 2:8 are associated with Store 1, rows 9:15 are associated with records from store 5 and row 16 is associated with records for store 50 (stores could have varying record numbers).
Desired output would be 3 new .csv files that will output to a directory: \\Share\Load\Import with the store number as worksheet name along with store name and date appended as the filename. So, using example above post script would generate 3 unique files:
Store_1_08212023.csv (would contain 8 rows of records including header)
Store_5_08212023.csv (would contain 8 rows of records including header)
Store_50_0821203.csv (would contain 2 rows of records including header)
Anyone have a script to share that could perform such actions? Thanks again for any assistance!
