VFP6 automation: Copy dbf with 20,000 records into Excel 2003 1

[Concentra2007]

We have a monthly program in FP6 that manipulates data in Foxpro dbf files, then converts them to Microsoft Excel and formats them for end users. One of our dbf files is over the 16,000ish record limit that Foxpro is assuming Microsoft Excel has (though Excel 2003 can go up to 65,000 records). FP is cutting off our data at the expected limit.
This is the main problem I would like suggestions for.

The following paragraphs outline the idea I tried and the specific problems I am having with it.

I am experimenting with writing the FP code to divide the dbf into two Excel files, then combine the two using "With Excel" commands in FP. The divide is easy and I can open the first file no problem, I just can't figure out how to combine the second file into it. The commands with the Excel menus are Data|Import External Data|Import Data.

To get ideas, I recorded the above keystrokes in a macro in Excel:Visual Basic and copied it into FP. See below. Program|Compile returns syntax errors on the first (really long) line and the fourth. I assume they have commands that FP6 doesn't know or aren't used correctly for FP6. But my FP knowledge doesn't go that deep. Any suggestions?

Thanks a bunch!

With .ActiveSheet.QueryTables.'Add'(Connection:=Array("OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=M:\Projects\Working\largefile\temphalf.xls;Extended Properties=""HDR=YES;"";Jet OLEDB:Engine Type=34"),Destination:=Range("A" + ALLT(STR(m.findcell))))
        .CommandType = xlCmdTable
      .CommandText = Array("temphalf$")
        .Refresh BackgroundQuery:=False
EndWith

 

[Mike Lewis]

Didn't you post this last week? I think I replied by asking you what code you use to do the export from Foxpro to Excel?

If you are using COPY TO .... TYPE XLS, then that's where the 16K limit comes in. If you use COPY TO ... TYPE XL5, then VFP will output 64K records.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Concentra2007]

Yes. I did post this last week and I received an email that you replied - thank you. But I also received a notice from tecumseh (tek-tips.com) asking me to move the question to a different forum. When I couldn't find my original post or your reply, I reposted the question to this forum as requested.

Actually, we are using "COPY TO . . . TYPE xl5." Our code is lowercase, but I just tested uppercase and got the same results: only 16,384 records (out of 17,592) were copied to Excel. Do you have any other ideas?

Thanks again.

 

[TamarGranor]

Help for VFP 9 says this:

Though you can export a maximum of 65,535 rows, which includes one row reserved for the field header, versions of Excel earlier than 8.0 (Excel 97) display only the first 16,384 rows and cannot import files containing more than 32,767 rows.

Tamar

 

[Concentra2007]

Thanks, Tamar. I am using a newer version of Excel than the ones the help file listed: Excel 2003; but I am using an older version of VFP: 6 rather than 9.

Does anyone have any ideas for the code to copy one Excel file into another one, ie what I can do to get around the syntax errors I'm getting on the above code in VFP6?

 

[dbMark]

My experience is more with importing than exporting. The latests Excel 2007 broke the VFP import capabilities when it introduced XML based structures that VFP couldn't understand. Our solution was to switch to drop the simple one line commands of the old days and move into objects. So we import now using a combination of these objects:

objExcelConn = CREATEOBJECT('ADODB.Connection')
objSchemaRcdSet = CREATEOBJECT('ADODB.Recordset')
objSheetRcdSet = CREATEOBJECT('ADODB.Recordset')
objExcelConn.ConnectionString = [Provider=Microsoft.ACE.OLEDB.12.0;Data Source=]+{myExcelfile}+[;Extended Properties='Excel 12.0;HDR=No;IMEX=1']
objExcelConn.Open
...

The problem is you want something simple, as in "Why can't it do one simple thing?" Excel has a legacy of limited rows. It's better now, but the import/export issue is so hard.

Here's an idea. Have you tried exporting with COPY TO {myTempFile} TYPE CSV and the opening Excel and importing the file? CSV files are becoming a standard intermediate file between systems. Just be sure that the field names don't become row 1 in Excelin which you lose or insert the first record in the conversions.

 

[Mike Gagnon]

Take a look at faq184-2617

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
ReFox XI (www.mcrgsoftware.com)

 

[Concentra2007]

Folks - I'm not sure how to close a post and indicate that the question has been resolved, but I feel good about the feedback I've received. Thank you so much.

 

[baltman]

You may find this to be faster/more flexible.
faq184-4704