Importing Excel worksheets into Visual Foxpro
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.Applic
oWorkBook = oExcel.WorkBooks.Open(rat_
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
Whenever I use the OLE automation to get the names of the tabs like so..
oExcel = CREATEOBJECT("Excel.Applic
oWorkBook = oExcel.WorkBooks.Open(rat_
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
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
How To Append Data from a Single Sheet of an Excel Workbook
http://support.microsoft.com/kb/176894/en-us
ASKER
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
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.
I'm still thinking on the share violation issue.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
Carl, Captian,
Thanks for your input. Captian is correct. I went down your code:
oExcel = CREATEOBJECT("Excel.Applic
oWorkBook = oExcel.WorkBooks.Open(rat_
oExcel.Visible = .F. && This is OK
lnSheetCt = oWorkBook.Worksheets.Count
import from rat_loc+"\"+r_file xl5 sheet(oWorkBook.Worksheets
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
Thanks for your input. Captian is correct. I went down your code:
oExcel = CREATEOBJECT("Excel.Applic
oWorkBook = oExcel.WorkBooks.Open(rat_
oExcel.Visible = .F. && This is OK
lnSheetCt = oWorkBook.Worksheets.Count
import from rat_loc+"\"+r_file xl5 sheet(oWorkBook.Worksheets
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
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.
Try to add one line in between what you already have and see if your situation improves.
oExcel.Quit()
oExcel=NULL
release oExcel