How to create / save a data table as a new worksheet in excel?

[#1]

I can't figure out how to save a data table as a new worksheet in an excel file.

I can create the Excel file if it doesn't exist, but I can't seem to create a worksheet inside of it. 

Here is what I have (sorry if it's a little sloppy):

ExcelFilePath = "C:\Temp\";

ExcelFileName = "WO " || "276" || " Summary.xls";

Path = ""||ExcelFilePath||""||ExcelFileName||"";

CurrentPart = "NewTab";

Try(

     dtExistingFile = Open(""||Path||"");

     Close(dtExistingFile, no save),

     dtNewFile = New Table();

     :Column 1<<Set Name("Counter");

     dtNewFile<<Add Rows(1);

     :Counter[1] = "Targets";

     Close(dtNewFile, save(""||Path||"")),

);

dtOld = New Table();

:Column 1<<Set Name("Counter");

dtOld<<New Column("Run Wafer");

dtOld<<Add Rows(1);

:Counter[1] = "Target";

ExcPref = Char(Arg( Parse((Char( Get Preferences( Excel Selection ) )) ), 1 ) );

Set Preferences( Excel selection( 0 ) );

dtOld<<Save Database("DSN=Excel Files;DBC="||path||";Default Dir="||ExcelFilePath||";DriverID =
1046;MaxBufferSize=2048;PageTimeout=5;",CurrentPart);

Close(dtOld, no save);                  

Set Preferences( Excel selection( ExcPref ) );

I've looked over some info here but when I run it I get :

[Microsoft][ODBC Excel Driver] The Microsoft Access database engine cannot open or write to the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view and write its data.

...in the log.  I have double checked and there are no permission issues with the directory of file.  Can anyone offer some advise?

[#2]

Seems like it would be simpler to just save the file directly to Excel.  Also I'd save it in .xlsx format - smaller files.

ExcelFilePath = "C:\Temp\";

ExcelFileName = "WO " || "276" || " Summary.xlsx";

file_Path = ExcelFilePath || ExcelFileName;

CurrentPart = "NewTab";

dtOld = New Table();

:Column 1 << Set Name( "Counter" );

dtOld << New Column( "Run Wafer" );

dtOld << Add Rows( 1 );

:Counter[1] = "Target";

dtold << save(file_path);

When I run your code I get an architecture mismatch with my Excel ODBC driver.  Must be a problem on my PC.

Anyway if you must use the Excel ODBC driver, change Path to something else, like file_Path.  Path is a JSL function.

Is your DSN string correct?  To verify this:

Click on File > Database > Open Table.  Select Machine Data Sources and click on Excel Files.  Find an Excel file and open it up.  In the dataset, click on Source > Edit.  Copy the DSN string from the source.

[#3]

Thanks,

Sorry I didn't explain better, this is a truncated version of my script for testing (refining?), the full version finds a list of part numbers and I would like it to loop through creating tabs named the same as the part number in the excel file.

My exact code from the  DSN confirmation is:

Open Database(

DSN=Excel Files;DBQ=C:\Temp\RunList.xlsx;DefaultDir=C:\Temp;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;",

"SELECT * FROM `Test$`",

"Test$"

)

Everything Looks OK there to me.  I will probably concede and just generate separate Excel documents for each part but I thought there might be something simple I was missing to get it to create / save as a new tab.  

[#4 · Accepted solution]

I don't know if this will help but in 2011 using JMP 9,  I wrote this fragment to test the creation of new sheets in an Excel file.Unfortunately

1. I can't recall if it worked! (I think it did)

2. I can't test it because I have 64 bit JMP installed but my MS ODBC drivers are 32 bit.

Something like the commented out line may be necessary to update an existing sheet but I think I was just wanting to see if I could get rid of a default sheet.

connectdlg =

"DSN=Excel Files;DBQ=C:\Try\Demoxlsql.xlsx;DefaultDir=C:\Try;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;";

dt = Open Database( connectdlg, "SELECT * FROM `Sheet1$`" );

//open database(connectdlg, "drop table Sheet1$");

dt << save database( connectdlg, "NewSheet" );

[#5]

Ok I will try that, but I won't be able to until next week, I'll reply here Monday.

Thanks

[#6]

If I recall correctly, my attempt was partially motivated by this link:

[SOLVED] Outputting JMP tables to Excel Worksheets

Michael