cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
dkeshock
Level III

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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
mpb
mpb
Level VII

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

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" );

View solution in original post

5 REPLIES 5
pmroz
Super User

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

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_PathPath 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.

dkeshock
Level III

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

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.  

mpb
mpb
Level VII

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

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" );

dkeshock
Level III

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

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

Thanks

mpb
mpb
Level VII

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

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

[SOLVED] Outputting JMP tables to Excel Worksheets

Michael