Did that actually work. I don't think it's the same for SQL Server 2000, but I need to automate saving the worksheet of 3 work books into individual .csv files for upload into SQL 2000 through DTS. There end up being 32 individual worksheets, so many there is a better way? Or many I should delete null rows, and then check for null worksheets, and if they are not null then create .csv files?
but I'm actually try to separate each worksheet within the workbook in order to save them as .csv files so that I can use DTS to umport them into SQL Server 2000. Unfortunately, I'm not using MS Access anymore. So I need to be able to read individual worksheets and if they are not empty, save them to .csv files based on the worksheet.name (usiing the worksheet name as the new csv file name).
Are you trying to have this in a program to import each tab (worksheet) within the workbook to be a seperate table?
Yes, I understand that I may be able to run a macro that will perform the blank row deletion, which is great! Then I'd like to check for empty worksheets so that if they are blank, they do not overwrite the ones that already exist in the database. I think the export to csv file can be omitted since DTS will actually import directly from MS Excel, so now I just have to figure out how to make the macro and DTS look through all of the tabs/worksheets.
The worksheets each represent a table in the database.
Here's a thought. What you can do, and I've done before, is to have a worksheet in your workbook, that is a kind of driver for all your spreadsheets. In this worksheet, you have all your tab names and then like an active or inactive indicator. So like a 1 or 0 next to the tab names. The 1 can represent whether you want to import that spreadsheet. Then in Access you import this Active/Inactive worksheet first and run through it to determine which spreadsheets you want to import. Not sure if this in an option, but I thought I would present it to you. Let me know if you need any of the code to do this. Most of what I've got is actually a query that pulls out the active worksheets. But I do have some code.
It would be helpful to see some code that reads the sheets in the workbook. It seems like everytime I nail down what they are looking for the requirement changes. Now we're trying to write the records from a query into an existing excel template (so that we can preserve the data validation, drop downs, worksheet name, and headers). I wouldn't assume that that part would be to hard, it's the import part that's really got me. Because the data has to be scrubbed for blank records, the foreign key field should not be blank, and then a query has to be run to replace the old data with the new data.
I guess I need to study up on how ColdFusion calls or interacts with MS Excel, or should I be executing this elsewhere?
Here is my code that reads the different tabs in the workbook. What this actually does, is there is an import all ready complete above this code that imports this one worksheet from the workbook. And this worksheet has the other tab(worksheets) names on it. And also has whether it's active or not. Meaning whether I want to import it or not. So there's another query that only pulls the tab names from the imported worksheet that have an active status into another table. So I'm only working with the tab names that have an active status. So this is the loop that it goes through to read the tab names needed for the import, imports it, then it deletes that name from the table to continue onto the next tab name.
Set dbs = CurrentDb
Cnt = 0
strSQL = "SELECT * FROM [Tab_Names]"
Set rst = dbs.openrecordset(strSQL)
tab_count = DCount("[Tab Names]", "Tab_Names")
For tab_loop = 0 To tab_count - 1
Set rst = dbs.openrecordset(strSQL)
Set current_tab = rst![tab names]
tab_err_flag = True