Switching EXCEL windows using a cell reference

Originally Posted by Kenneth Hobson

It is easy to do. If you post example csv files and what the import should look like in the Excel file in a zip file.

I have no objection to posting my CSV files, but I fail to see why you need my files specifically.

The CSV files and their final format is not the problem here. It is the macro control of switching windows (not sheets) within the workbook.

Create 2 simple CSV files of any data, any number of rows and columns.
Name them as such "SU-modelname-components" and "SU-modelname-layers". There will be 16 of these in my final spreadsheet.

Create a spreadsheet with 3 sheets, named "Macros", "components" and "layers"

Define on the Macro sheet 5 cells and range name them as follows;
"SUfilepath" containing the path to your CSV files - ie: "D:/sufiles/"
"SUmodelname" containing the name of your CSV files "SU-modelname".
"SUfilename" concatenating the file path & model name i:e "D:/sufiles/su-modelname"
"CSV1file" containing the cell ref to "SUfilename"&"components.csv". It should look like "D:/sufiles/SUmodelname-components.csv"
"CSV2file" containing the cell ref to "SUfilename"&"layers.csv". It should look like "D:/sufiles/SUmodelname-layers.csv"

Now create a macro that does the following, BUT has no filenames hardcoded within the macro. It MUST read those names above from the sheet, as the models will change almost every run, as will the CSV data contents, but the formats and file type (ie: "-components" or "-layers") remain constant.

Import the "components" CSV file. ("CSV1file") This will open a new window named "SUmodelname-components.csv"
Copy the CSV data to the "components" sheet.
Close the Components CSV window. I had to switch to the sheet to close it, but "Windows().activate" won't read the name from a rangename.
Repeat for the "layers" CSV file.

Is there another way to close a window, or better yet a way to load a CSV file directly into a specified sheet??