Determining if multiple EXCEL spreadsheet tabs exist before import

Is there a way to determine if an EXCEL spreadsheet contains multiple tabs, and if so, bring back the name of each tab in order to select the one that you want to import data from

I'm trying to import data into VFP from EXCEL, and normally use the IMPORT function and identify the spreadsheet tab by name. This time, I want to know 1) the total number of tabs in the spreadhsheet, and 2) the names of the various tabs found in the spreadsheet. This will let me choose which one I'd like to import the data from.

Any help or suggestions to accomplish this would be appreciated.

Thanks.

Yes, you can do it through Excel automation.

Naomi,

First, thanks very much for your response! I am a novice when it comes to writing code; I only understand to a limited degree what your code is doing. Can this solution be run programmatically If so, how

GR1206 wrote:

Naomi, First, thanks very much for your response! I am a novice when it comes to writing code; I only understand to a limited degree what your code is doing. Can this solution be run programmatically If so, how

To run code, you copy it to a prg file (simple text file), or to a method of a form or other object which you invoke.

The sample uses COM Automation to get an instance of MS Excel running, then opens a spreadsheet named in a parameter you pass as "tcInputFile". It then extracts the count of how many sheets in the workbook into a memory variable.

The incomplete section (as of this writing), shows the beginning part of a loop to traverse all the sheets from 1 to lnNumSheets, selecting each one in turn, and then extracting the name of the sheet. You could continue and do whatever you see fit with them.

HTH

Alex answered your question in details, sorry, I was not checking this thread. I posted some code from my working Excel2DBF routine.

Yes, you can definitely put this into program, just add the endwith and next statements to close the for each and with blocks. You also need to add your own processing here.

I'm also not sure that this sample uses the best techniques, but at least it's a working sample. tcInputFile is a name of Excel (with full path), tcPassword is a password for the Excel file in case it's password protected. You may remove all parameters and use only the first one.

Hope this answers the question.

Alex and Naomi,

Thanks very much for both your explanations of the code; I'm going to incorporate in into a piece of code that I wrote previously that allows me to retreive multiple spreadsheets; I neglected to mention that I'm looking at over 100 spreadsheets, examining them all for multiple tabs, and retrieving the contents based on the name of the tab.

Thanks again!!!

GR1206 wrote:

Thanks very much for both your explanations of the code; I'm going to incorporate in into a piece of code that I wrote previously that allows me to retreive multiple spreadsheets; I neglected to mention that I'm looking at over 100 spreadsheets, examining them all for multiple tabs, and retrieving the contents based on the name of the tab.

Remember to clean up after you are done. When you open a reference to an instance of Excel, it is live in memory, even if it is not visible in the taskbar below. You control this with the Visible property: oExcel.Visible = .T.

You do not need to show it to the user to run it, but it is in memory and needs to be released when you don't need it anymore.

For VFP8 and VFP9 I'm using this structure:

Knowing the tabs in excel wouldn't help you much. An alternative is to get all data into cursors first and do whatever later. ie:

lcXLS = GETFILE("xls") 
lcConn = "Driver={Microsoft Excel Driver (*.xls)};"+;
 "DriverId=790;Dbq="+m.lcXLS+;
 ";DefaultDir="+JustPath(m.lcXLS)+";" 
lnHandle = SQLStringConnect(m.lcConn) 
SQLTables(lnHandle,"", "SheetNames")
Scan
	lcTableName = Trim(SheetNames.Table_Name)
	lcOutput = Chrtran(m.lcTableName,' $','_')
	SQLExec(lnHandle,'select * from ['+m.lcTableName+']',m.lcOutput )
endscan
SQLDisconnect(lnHandle)

The code "pasted" won't work... You may try (in vars you will have active sheet name) :

Alex,

Thanks very much for the tip on releasing the objects; I've tried several commands to do it, but cannot seem to make it work. I've tried adding multiple release statements to address the various objects being referenced, but I'm not having much success. Here's the code as it stands now:

Any help you could provide on releasing the objects referenced would be greatly appreciated.

Thanks.

Instead of

release lnNumSheets,loSheet,loExcel EXTENDED

use

loExcel.Quit()

loExcel = NULL

GR1206,

You are making it too complicated and you are not quitting Excel. Try this simple example (change the path and name to your file)

Thanks for the suggested changes, Alex. I tried implementing them, but it didn't work at first. I had to add additional RELEASE statements for the other objects being referenced. (see below)

GR1206 wrote:

Thanks for the suggested changes, Alex. I tried implementing them, but it didn't work at first. I had to add additional RELEASE statements for the other objects being referenced. (see below) Code Snippet loExcel.Quit() Release loSheet Release lnNumSheets Release lcStr Release loExcel loExcel = NULL Not sure how much of the above is necessary, but the code works now, and I'm quitting EXCEL.

The release statements are unnecessary, but they would not hurt anyway.

GR1206 wrote:

I've since added code to identify the contents of the tabs within the spreadsheets, and then export data from those tabs that have usable data. QUESTION: I'm referencing memory variables in an APPEND statement to pull data into a VFP template, can I can use a memory variable to reference the specific workbook (loWorkbook) and SHEET (osheet.name)

Yes, you definitely can use a memory variable for this and you can even pass it into a separate routine as a parameter.