Importing Excel worksheets into Visual Foxpro

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

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.

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.

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.

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
 

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

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.

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.