BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

How to open private data tables in loop

All, 
     I have a reasonably simple problem. It is my understanding that when you open the data table as private, you would lose the table when you don't retain the reference of the data table name. So, how do you retain the data tables especially when you are opening them as a part of a loop. For e.g.: 

 

FilePath = "C:\Test.xlsx"; 

SheetsList = Get Excel Worksheets(FilePath); 
OpenDts = {}; 

for(i = 1, i <= N Items(SheetsList), i++,
		dt = Open(FilePath,
					"Private",
					Worksheets(SheetsList[i]),
					Use for all sheets( 0 ),
					Concatenate Worksheets( 0 ),
					Create Concatenation Column( 0 ),
					Worksheet Settings(1,
										Has Column Headers( 1 ),
										Number of Rows in Headers( 1 ),
										Headers Start on Row( 1 ),
										Data Starts on Row( 2 ),
										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( "-" )
									  )
				 );
		Insert Into(OpenDts,"Data Table(" || SheetsList[i] || ")"); 
   );
Show(SheetsList); 
Show(OpenDts); 

// This fails for(i = 1, i <= N Items(OpenDts), i++, OpenDts[i] << Show Window(1); );
Best
Uday
1 ACCEPTED SOLUTION

Accepted Solutions
Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

Solution

Re: How to open private data tables in loop

To clarify, you don't need to try to insert into a different List (your "OpenDTS"). Use a list on the left side of the Open() statement, in place of "dt".

 

FilePath = "~/Desktop/Test.xlsx"; 

SheetsList = Get Excel Worksheets(FilePath); 
OpenDts = {}; 

for(i = 1, i <= N Items(SheetsList), i++,
		OpenDts[i] = Open(FilePath,
					"Private",
					Worksheets(SheetsList[i]),
					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( 1 ),
										Data Starts on Row( 2 ),
										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( "-" )
									  )
				 );
//		Insert Into(OpenDts,"Data Table(" || SheetsList[i] || ")"); 
   );
Show(SheetsList); 
Show(OpenDts); 
// This fails 
for(i = 1, i <= N Items(OpenDts), i++, 
		OpenDts[i] << Show Window(1); 
   );	
-Jeff
4 REPLIES 4
Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

Re: How to open private data tables in loop

You need to store your data table references in a list instead of reusing the dt variable.
-Jeff
Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

Solution

Re: How to open private data tables in loop

To clarify, you don't need to try to insert into a different List (your "OpenDTS"). Use a list on the left side of the Open() statement, in place of "dt".

 

FilePath = "~/Desktop/Test.xlsx"; 

SheetsList = Get Excel Worksheets(FilePath); 
OpenDts = {}; 

for(i = 1, i <= N Items(SheetsList), i++,
		OpenDts[i] = Open(FilePath,
					"Private",
					Worksheets(SheetsList[i]),
					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( 1 ),
										Data Starts on Row( 2 ),
										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( "-" )
									  )
				 );
//		Insert Into(OpenDts,"Data Table(" || SheetsList[i] || ")"); 
   );
Show(SheetsList); 
Show(OpenDts); 
// This fails 
for(i = 1, i <= N Items(OpenDts), i++, 
		OpenDts[i] << Show Window(1); 
   );	
-Jeff
uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

Re: How to open private data tables in loop

@Jeff_Perkinson

       It still fails.  I used the exact script and I do see that the data table references are stored into the list. However, when I try to make them visible, that still fails .

Best
Uday
uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

Re: How to open private data tables in loop

@Jeff_Perkinson
 You know what, I see the mistake, instead using the window command, I should have created a new data view since it is a private data table. 

FilePath = "C:\Test.xlsx"; 
SheetsList = Get Excel Worksheets(FilePath); 
OpenDts = {}; 

for(i = 1, i <= N Items(SheetsList), i++,
		OpenDts[i] = Open(FilePath,
					"Private",
					Worksheets(SheetsList[i]),
					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( 1 ),
										Data Starts on Row( 2 ),
										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( "-" )
									  )
				 );
//		Insert Into(OpenDts,"Data Table(" || SheetsList[i] || ")"); 
   );
Show(SheetsList); 
Show(OpenDts); 
// This works
for(i = 1, i <= N Items(OpenDts), i++, 
		OpenDts[i] << New Data View; 
   );	
Best
Uday