Switching EXCEL windows using a cell reference

I am getting frustrated. This was so simple in LOTUS123, but I can't get it to work in EXCEL (2000). I admit I do not have a lot of experience with EXCEL macros and VBA.

Here's what I'm trying to do.

I am using Google Sketchup and I have a data extract app that produces several CSV files (as many as 16). From those CSV files I will setup an RDB to produce various reorts about the 3D model.

The CSV files are named by concatenating the Sketchup filename and the type of CSV file, ie Sketchup-model_components.csv or Sketchup-model_layers.csv. The filename changes, but the 16 CSV file types stay constant. In this manner I know exactly the file names.

So I need a macro to import each CSV file directly into a spreadsheet and do some basic reformatting, each file, 1 by 1. I want to make a single macro to do all 16, and read rangenamed worksheet cells to control placement and housekeeping.

It amazes me that EXCEL will not import a CSV file directly into a spreadsheet, and has to open a 2nd window to do it. That was not a problem in Lotus123.

The problem I am facing is EXCEL macros do not easily read the spreadsheet for control. I have modified a macro to read the filename from a cell using Range("rangename") but I cannot get the switch windows command to read a similar rangename.

CSV files open in separate windows and are named with the CSV filename. I then copy the data over to the appropriate sheet in the main spreadsheet.
I need to go back to that exact CSV window to close it.

Can't do it. I cannot seem to make the command "windows("windowname").activate" accept a cell ref to select the CSV window.
I was able to get the "Workbooks.Open Filename:=Range("CSV1filename")" to work, but not the windows command.

Any Ideas??

Now there is a 2nd way to get the data in using a .TXT file.
But I have 2 problems with that.
1st, the data extractor is .CSV only and changing 16 .CSV extensions to .TXT every run is a non-starter.
2nd, importing a TXT file opens the dialog box every time.

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??