Subscribe Bookmark RSS Feed

open excel multiple worksheets column name row 2

tsolomon

Community Trekker

Joined:

Oct 28, 2015

I am able to open multiple files and specify no header with "Table Contains Column Headers (0)". However, this does not seem to work when trying to open an excel file with multiple worksheets. I tried placing the comment in several locations to no avail. Any ideas?

raw_dt1 = Open(

"C:\Users\ts\Desktop\bacteria testing\Bacteria.xlsx",

Worksheets( {"Test Supply", Table Contains Column Headers( 0 )}));

Thanks

TS

6 REPLIES
txnelson

Super User

Joined:

Jun 22, 2012

Here is some code generated by the JMP Excel Wizard, which opens an Excel workbook with multiple spreadsheets and no headers:

Open(

"C:\Users\txjim\Documents\sample.xlsx",

Worksheets( "Sheet2" ),

Use for all sheets( 1 ),

Concatenate Worksheets( 0 ),

Create Concatenation Column( 0 ),

1,

Has Column Headers( 0 ),

Number of Rows in Headers( 1 ),

Headers Start on Row( 1 ),

Data Starts on Row( 1 ),

Data Starts on Column( 1 ),

Data Ends on Row( 0 ),

Data Ends on Column( 0 ),

Replicated Spanned Rows( 1 ),

Suppress Hidden Rows( 1 ),

Suppress Hidden Columns( 1 ),

Suppress Empty Columns( 1 ),

Treat as Hierarchy( 0 )

));

Jim
tsolomon

Community Trekker

Joined:

Oct 28, 2015

Hi Jim, thanks for the email. I tried this and it still does not work for

me. The first row is still selected as the column names.

tsolomon

Community Trekker

Joined:

Oct 28, 2015

Hi Jim, while that code didnt work. I did not know about the excel import

wizard, once I used that and outputted the code, I was able to get it to

work with this code.

Open(

"C:\Users\tsolomo1\Desktop\bacteria testing\testing.xlsx",

Worksheets( "Sheet1" ),

Use for all sheets( 1 ),

Concatenate Worksheets( 0 ),

Create Concatenation Column( 0 ),

Worksheet Settings(

1,

Has Column Headers( 0 ),

Number of Rows in Headers( 1 ),

Headers Start on Row( 2 ),

Data Starts on Row( 2 ),

Data Starts on Column( 1 ),

Data Ends on Row( 0 ),

Data Ends on Column( 0 ),

Replicated Spanned Rows( 1 ),

Suppress Hidden Rows( 1 ),

Suppress Hidden Columns( 1 ),

Treat as Hierarchy( 0 )

)

)

Thanks for your help

TS

ms

Super User

Joined:

Jun 23, 2011

Yes, the custom Excel worksheet settings must be enclosed by the the Excel Settings( ) command. The scripting index is potentially misleading here (JMP 12) as it in Example 3 for the Open() command gives the (commented out) option as Excel Options( ) that does not seem to work (JMP 12 for Mac); the first row is included as default.

txnelson

Super User

Joined:

Jun 22, 2012

what version of JMP are you using?

Jim
tsolomon

Community Trekker

Joined:

Oct 28, 2015

I have JMP Pro 11.1.1