We help IT Professionals succeed at work.

Importing Excel worksheets into Visual Foxpro

Rick100Lynne
Rick100Lynne asked
on
Medium Priority
8,144 Views
Last Modified: 2010-08-05
I have sucessfully been using a command line to import data from an Excel worksheet(tab) and modify that data inside Foxpro V5. Now, the Excel Spreadsheet has multiple worksheets that I need to import. This is an automated program with no human intervention. I needed to get the names of the tabs in order to use the import command line with the SHEET parameter. This is what I use..."import from rat_loc+"\"+r_file xl5 sheet("Persons 25-54 (1)")". The rat_loc+"\"+r_file are varibles that I use to locate the xls file.
Whenever I use the OLE automation to get the names of the tabs like so..
oExcel = CREATEOBJECT("Excel.Application")
oWorkBook = oExcel.WorkBooks.Open(rat_loc+"\"+r_file)
oExcel.Visible = .F.
I then store the sheets to an array and close out the automation by using:
oExcel.Quit()
release oExcel
release all like o*
I then try to use my import command but I keep encountering a OLE share error. Is there something else I need to close? Is there a similar automation command that will duplicate the import?

Thanks in advance for your help.

Rick

Comment
Watch Question

Sometimes an object reference gets hung up.

Try to add one line in between what you already have and see if your situation improves.

oExcel.Quit()
oExcel=NULL
release oExcel
As far as importing, you seem to have the only viable way down to get that done.

How To Append Data from a Single Sheet of an Excel Workbook
http://support.microsoft.com/kb/176894/en-us

Author

Commented:
Carl,
Thanks for your input. I tried your three commands, but I still got a OLE 0x80030020 share violation. As for the Singl sheet import, I looked at the link and it showed the same command I mentined in my request: This is what I use..."import from rat_loc+"\"+r_file xl5 sheet("Persons 25-54 (1)")".

Rick
On the import-- yes, that is why I said you seem to have the only viable way to get that part done.

I'm still thinking on the share violation issue.
Try to run this bit of unvarnished code, with your two variables initialized to locate the filed and name, and see what results it gives you:

oExcel = CREATEOBJECT("Excel.Application")
oWorkBook = oExcel.WorkBooks.Open(rat_loc+"\"+r_file)
oExcel.Visible = .F.

lnSheetCt = oWorkBook.Worksheets.Count

import from rat_loc+"\"+r_file xl5 sheet(oWorkBook.Worksheets.Item(1).Name)

FOR ii = 2 TO lnSheetCt
      APPEND from rat_loc+"\"+r_file xl5 sheet(oWorkBook.Worksheets.Item(ii).Name)
ENDFOR

oExcel.Quit
oWorkBook=""
oExcel=""
RELEASE oWorkBook,oExcel
BTW, this bit of code assumes all sheets have the same format of data and that each sheet puts all of its data into the one table name.

Since I don't have your data or know your complete intent, I couldn't figure out whether or not you really want all of the data in one dbf as I have listed and as your IMPORT FROM command would do, OR whether you wanted each sheet's data in a separate table, in which case the IMPORT FROM command would have to have the r_file value adjusted to a different name so as to not overwrite the previous IMPORT command.

I hope this helps.
CaptainCyrilFounder, Software Engineer, Data Scientist

Commented:
What you need to do is read the sheet tabs names and store them in a cursor. Close the Excel Worksheet using oWorkBook.Close and then oExcel.Quit and then import the sheets. Otherwise you will have a share violation because you will have the worksheet open from 2 different places.

Author

Commented:
Carl, Captian,
Thanks for your input. Captian is correct. I went down your code:

oExcel = CREATEOBJECT("Excel.Application")  && This is OK
oWorkBook = oExcel.WorkBooks.Open(rat_loc+"\"+r_file)  && This is OK
oExcel.Visible = .F. && This is OK

lnSheetCt = oWorkBook.Worksheets.Count && This is OK

import from rat_loc+"\"+r_file xl5 sheet(oWorkBook.Worksheets.Item(1).Name) ---------- OLE error code 0x80030020: A share violation has occurred.

Captian, to answer your comment, this program is going into a business where the program has to read the tabs without any human intervention. The tabs on the spreadsheet they get can be from 1 to 50 different titles, but only 10 tabs per spreadsheet. The data format in each tab is identical.

Carl, I tried to close, release, null out all object references and then tried to import but still get the share violation.

I am still experimenting. Thanks

Rick
 

Author

Commented:
Carl, Captian,
I created an EXE outside Foxpro that looks at the sheets and creates a txt file of the sheet names. I then ran Fox interactively to open the program. It still gave an error violation. I then quit out of Foxpro and tried to delete the file. The system stated that Excel is running and has hooks on that file. That must be what is doing this. Since we stated:
oExcel.Visible = .F.
Excel does not appear for the user, but is still running even when we attempt to release all hooks through Foxpro. Even when Fox is completely shut down, Excel is still running in the background. How can we stop Excel from running remotely through Fox?

Thanks for helping!

Rick
Commented:

oExcel.Quit   && This will close the excel application
Since the code worked for me with a share violation, you must have something odd about your configuration that I cannot debug sitting at my computer.  I ran it here against a spreadsheet that contains 20 separate sheets and no sharing violation was thrown out.
CaptainCyrilFounder, Software Engineer, Data Scientist
Commented:
Rick,

oExcel.Visible = .T. should bring the Excel visible.

What you need to do is you can count all the tabs in the Excel sheet and store them in a cursor.

For i = 1 TO oExcel.Workbook.Sheets.Count

get the names
store them in a cursor
close the workbook
shut down excel

SELECT cursor && this is where the sheet names are stored
SCAN ALL
    SELECT importcursor
    APPEND FROM ... && append from the excel sheet
ENDSCAN
What version of Visual FoxPro are you using?

Do you have more than one version of Excel on that PC or have you upgraded it to a newe version where more old entries could still reside in the registry also referencing an older version of Excel?

As I said, the code I listed works for me and does NOT create a share violation of any kind.

Also, I earlier mentionened a reference to the fact that the IMPORT FROM command will try to overwrite the first successful IMPORT FROM attempt because you are using the same file name in the way you initially had it setup.  Do you want separate tables/dbfs for each tabbed sheet within the spreadsheet?  OR do you want a separate table for each tabbed sheet.  If you want separate tables, you will have to change the value of r_file for each IMPORT FROM.  If you want all of the data in one table, you only issue IMPORT FROM one time to create the initial table and then use APPEND FROM to get the other tabbed sheets into that one newly created and now already open r_file table.
One more thought, since you seem to have a quick OLE sharing violation error, do you have anything other than text in the initial tabbed sheet like a graphics image or some odd embedded object?  This is angle beyond the multi-versions of Excel that could present a problem.  But, again, I dson't have your data and my test on Excel spreadsheet with data only contained in 20 tabbed sheets worked without error.
Thanks.

I'm not sure which part put you over the edge into a solution.  But, enough was covered where something should have helped the light bulb to come on.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.