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

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

dkeshock — Thu, 14 Aug 2014 18:53:49 GMT

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?

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

pmroz — Thu, 14 Aug 2014 21:34:57 GMT

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.

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

dkeshock — Fri, 15 Aug 2014 14:39:51 GMT

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.

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

mpb — Fri, 15 Aug 2014 18:31:58 GMT

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

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

dkeshock — Fri, 15 Aug 2014 18:43:59 GMT

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

Thanks

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

mpb — Fri, 28 Oct 2016 13:13:13 GMT

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

[SOLVED] Outputting JMP tables to Excel Worksheets

Michael