FoxPro Excel Automation 1

[waynedp]

I am having 2 minor problems, first, I can't open an excel workbook:

oExcel = CreateObject("Excel.Application")
oWorkbook = oExcel.Workbooks.Open("C:\WDPQA.xls")

I have even tried
oWorkbook = oExcel.Application.Workbooks.Open("C:\WDPQA.xls")

The funny thing is I will comment out the oWorkbook code is this will work

oWorkbook = oExcel.Application.Workbooks.Add()
oExcel.Visible = .T.

I can add a new workbook, but can't open an existing one. Yes, I am using a 32-bit MS Excel

Second,

oSheet = oExcel.ActiveSheet
oSheet.Range("B1").Value = "C:\Users\wayne.perkins\Documents\Visual FoxPro Projects\QA\29484Q1.DBF"

sDatabase = "C:\WDPQA.DBF"
sSqlstring = "SELECT COUNT(*) FROM " + sDatabase
oQryTable = oSheet.QueryTables.Add("OLEDB;Provider=VFPOLEDB.1;Data Source=" + sDatabase + ";",oSheet.Range("B2"),sSqlstring)
oQryTable.FieldNames = .F.
oQryTable.RefreshStyle = 0
oQryTable.Refresh = .T.

I get an error on oQryTable.Refresh = .T.


Please help. Thank you.

Wayne

 

[JRB-Bldr]

Have you looked at:

Excel - How to do Automation from VFP​

http://www.tek-tips.com/faqs.cfm?fid=4428

Note where the line: oExcel.Visible = .T. is used.
I have found this to be very helpful in being able to actually watch Excel being manipulated by Automation.

While it may not answer all of your questions, it should at least get you started.
After that, you can come back with specific questions.

Good Luck,
JRB-Bldr

 

[TamarGranor]

I suspect your problem might be related to trying to open a file in the root folder of C. Try opening a workbook that's somewhere else on the drive.

Tamar

 

[waynedp]

Sorry, I meant oQryTable.Refresh = .T. is when I get error from on the QueryTable, and yes I have tried other folders, like I said, I am able to add a workbook, but not open an existing work, as for the refresh, how am I to get my data displayed if I can refresh

 

[vgulielmus]

Like Tamar Granor, I also suspect the readonly state.
Please read Workbooks.Open Method
Pay attention to several parameters, like IgnoreReadOnlyRecommended, Notify or CorruptLoad (but may be other that solves your problem).
As a final thought, have you tried to open the workbook in Excel, no programmatically, but from the open dialog box?

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

http://praisachion.blogspot.com/

 

[waynedp]

First, I want to deal with one issue at time, yes, I have read the Workbooks.Open Method, and yes, I have read Excel - How to do Automation from VFP, have done automation before

oExcel = Createobject("Excel.Application")
With oExcel
.Workbooks.Open("C:\Users\WDP\Documents\Visual FoxPro Projects\QA\WDPQA.XLS")
.Visible = .T.
EndWith

I get error 0x80010105 on the Workbooks.Open. I have opened the file in Excel, it opens fine. I am just puzzled as to why I am can't thru automation open any workbooks. In my Reference Libraries, it is currently using C:\Windows\SysWOW64\stdole2.tlb, instead of C:\Windows\System32\stdole32.tlb. Could this be the problem?

 

[vgulielmus]

First of all, I apologize, no offense intended.
I'm pretty sure you already know that SysWOW64 is for 32 bit applications under 64 bit OS. Almost certain this is not the cause of your problems.

You mentioned that
oWorkbook = oExcel.Application.Workbooks.Add()
oExcel.Visible = .T.
runs with no errors.

Have you tried ?
oExcel.Visible = .T.
oWorkbook = oExcel.Workbooks.Open("C:\WDPQA.xls")

Also have you tried to turn the calculation to manual?
Maybe it took too long to open the workbook.

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

http://praisachion.blogspot.com/

 

[waynedp]

Thank you so much Vilhelm, I just tried your suggestion

oExcel.Visible = .T.
oWorkbook = oExcel.Workbooks.Open("C:\WDPQA.xls")

It worked, now just on more question

sSqlstring = "SELECT COUNT(*) FROM " + sDatabase
oQryTable = oSheet.QueryTables.Add("OLEDB;Provider=VFPOLEDB.1;Data Source=" + sDatabase + ";",oSheet.Range("B12"),sSqlstring)
oQryTable.FieldNames = .F.
oQryTable.RefreshStyle = 0
oQryTable.Refresh = .T.


I am getting an error on "oQryTable.Refresh = .T.

 

[waynedp]

And Thank you JRB-Bldr,


Now the light bulb went off on you comment, and thanks again Vilhelm

 

[waynedp]

I am thinking, something wrong with my connection string

oSheet = oExcel.ActiveSheet
sDatabase = GETFILE("DBF","","",0,"Select [Q] File Database To Process")
sConnString = "OLEDB;Provider=VFPOLEDB.1;Data Source=" + sDatabase + ";"
sSqlString = "SELECT COUNT(*) FROM " + sDatabase
oQryTable = oSheet.QueryTables.Add(sConnString,oSheet.Range("B10"),sSqlString)
oQryTable.FieldNames = .F.
oQryTable.RefreshStyle = 0
oQryTable.Refresh = .T.

I am getting an error here: oQryTable.Refresh = .T.


Please help. Thanks. Wayne

 

[Olaf Doschke]

A Database normally is a DBC and separate from all its DBFs. To have a single DBF as Data Source you'll need another connection string with Data Source just being the path to a DBF/ all your DBFs, [tt]sConnString = 'OLEDB;Provider=VFPOLEDB.1;Data Source="' + JustPath(sDatabase) + '";'[/tt]
and then [tt]sSqlString = 'SELECT COUNT(*) FROM ("' + JustFName(sDatabase)+'")'[/tt]

So for example a DBF at C:\my tables\my.dbf will literally need

...
sConnString = 'OLEDB;Provider=VFPOLEDB.1;Data Source="C:\my tables\";'
sSqlString = 'SELECT COUNT(*) FROM ("my.dbf")'
...

Bye, Olaf.

 

[waynedp]

Hello Olaf,


I have tried your suggestion, I am still receiving an error QryTable.Refresh = .T.

oWorkbook = oExcel.Workbooks.Open("C:\WDPQA.xls")
oSheet = oWorkbook.Sheets(1)
oSheet.Activate
sDatabase = GETFILE("DBF","","",0,"Select [Q] File Database To Process")
sConnString = 'OLEDB;Provider=VFPOLEDB.1;Data Source="' + JustPath(sDatabase) + '";'
sSqlString = 'SELECT COUNT(*) FROM ("' + JustFName(sDatabase)+'")'
oQryTable = oSheet.QueryTables.Add(sConnString,oSheet.Range("B10"),sSqlString)
oQryTable.FieldNames = .F.
oQryTable.RefreshStyle = 0
oQryTable.Refresh = .T.

 

[Olaf Doschke]

What's the error message?

Bye, Olaf.

 

[waynedp]

Hi Olaf,

The error code is OLE error 0x80020003: member not found. I have check the workbook properties, under data connections connection string is rovider=VFPOLEDB.1;Data Source="C:\USERS\WAYNE.PERKINS\DOCUMENTS\VISUAL FOXPRO PROJECTS\QA"; sql is SELECT COUNT(*) FROM ("29484Q1.DBF"), and they seem fine as per your last email regarding DBC and standalone Data Source, from my understanding you will get some type of error from querytable is something not correct in connection, destination, sql. Thanks.


Wayne

 

[Olaf Doschke]

Member not found", happening in the line [tt]oQryTable.Refresh = .T.[/tt] in the first place means, Refresh is not a member of the oQryTable object, doesn't it?. What does this have to do with the OLEDB driver or query at all?

All sample usages in of QueryTables.Refesh I find in sample code all over the net suggest, that Refresh is a method you call, not a property you set to .T.

Bye, Olaf.

Edit: Intellisense also tells, this is a method, not a property:

 

[waynedp]

Thank you so much Olaf,


Originally I stated this project in VBA, was converting in over to FoxPro because of some issues with fix length files, which I think I can do in FoxPro, again, Thank you, and thanks to everyone else for your assistance.