429451_en-US

liliconcom

Beginner : Help with opening single worksheet and worksheet setting

I have this script which allows me to select the worksheet as well as set the worksheet settings when I put in the file path : 

Open(
 "$DESKTOP/JMP practice/20210305_COVID-19-QC_RM_NTC_2134-001 (2).xlsx",
 Worksheets( "Amplification Data" ),
 Use for all sheets( 1 ),
 Concatenate Worksheets( 0 ),
 Create Concatenation Column( 0 ),
 Worksheet Settings(
  1,
  Has Column Headers( 1 ),
  Number of Rows in Headers( 1 ),
  Headers Start on Row( 49 ),
  Data Starts on Row( 50 ),
  Data Starts on Column( 1 ),
  Data Ends on Row( 0 ),
  Data Ends on Column( 0 ),
  Replicated Spanned Rows( 1 ),
  Replicated Spanned Headers( 0 ),
  Suppress Hidden Rows( 1 ),
  Suppress Hidden Columns( 1 ),
  Suppress Empty Columns( 1 ),
  Treat as Hierarchy( 0 ),
  Multiple Series Stack( 0 ),
  Import Cell Colors( 0 ),
  Limit Column Detect( 0 ),
  Column Separator String( "-" )
 )
);

 

But when I just try to write a script to let the user select a file to open with the command :

open ()

It just opens all the work book and not letting me edit the workbook settings, I need a command which lets me edit the worksheet settings even when the file path isn't mentioned . Please Help

AnalyticsData TableWindowsRe: Beginner : Help with opening single worksheet and worksheet setting

Use a Pick File() function to select the file to open, and then take the results of the Pick File and reference it in the Open() function.

theFile = Pick File(....);
Open( theFile,
other loading options);

Re: Beginner : Help with opening single worksheet and worksheet setting

Hi txnelson,

the pick file works well , but i am still having issues :

theFile = Pick File();

Open(
 "theFile",
 Worksheets( "Results"),
 Use for all sheets( 1 ),
 Concatenate Worksheets( 0 ),
 Create Concatenation Column( 0 ),
 Worksheet Settings(
  1,
  Has Column Headers( 1 ),
  Number of Rows in Headers( 1 ),
  Headers Start on Row( 49 ),
  Data Starts on Row( 50 ),
  Data Starts on Column( 1 ),
  Data Ends on Row( 0 ),
  Data Ends on Column( 0 ),
  Replicated Spanned Rows( 1 ),
  Replicated Spanned Headers( 0 ),
  Suppress Hidden Rows( 1 ),
  Suppress Hidden Columns( 1 ),
  Suppress Empty Columns( 1 ),
  Treat as Hierarchy( 0 ),
  Multiple Series Stack( 0 ),
  Import Cell Colors( 0 ),
  Limit Column Detect( 0 ),
  Column Separator String( "-" )
 )
);

Even when I put this it show's an error " There was an error opening the file in the excel wizard... using all the open all sheets preference should work in this case" I trying to work it out, hopefully will find a way to get it done, Thanks !