I use the Office 2007 Connectivity Pack which will work with XLS and
XLSX.
http://www.microsoft.com/downloads/details.aspx?FamilyId=6F4EDEED-D83F-4C31-AE67-458AE365D420&displaylang=en
Some snips of code:
This.SQLHandle = SqlStringConnect("Driver={Microsoft
Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" ;
+"DBQ=" +
FullPath(lcFile)+";")
lcSelect=[SELECT Date,Account,Name,Ref1,Value,Lodgement
FROM "Sheet1$"]
lnResult = SQLEXEC(This.SQLHandle, lcSelect, "_stage1_")
Where "_stage1_" is a cursor which gets the results.
Use in Select("_Stage1_")
This.DisconnectExcel()
On Wed, 02 Feb 2011 05:07 +0530, "Sytze de Boer" <[email protected]>
wrote:
> Mike
> I had a lot of issues with this myself
> In my instance, I needed to "append from"
>
> Amongst other things, this is what I did
>
> PARAMETERS xsht
>
> Try
> **mx is the name of the xl file
> IF xsht
> Append From (mx) Type Xl5 Sheet (msheet)
> ELSE
> Append From (mx) Type Xl5
> ENDIF
>
> Catch
> =Messagebox('Sorry, the system has a problem with that spreadsheet.
> The
> system will try and convert it',0,'')
>
> WAIT WINDOW NOWAIT "Converting the spreadsheet, patience please "
>
> SET SAFETY off
> oExcel = CreateObject("Excel.Application")
> oWorkbook = oExcel.Workbooks.Open("&mx")
> *nFileFormat = oWorkbook.FileFormat && Find out what file type source
> is
> *?nFileFormat && display results
>
> IF FILE("convertedmx.xls")
> DELETE file convertedmx.xls
> endif
>
> newfile=SYS(5)+CURDIR()+"convertedmx"
> oWorkbook.SaveAs('&newfile',39)
> ** the reference to 39 is the Excel format I want
> oExcel.quit()
>
> ....the rest
>
>
>
>
>
>
> On Wed, Feb 2, 2011 at 3:22 AM, MB Software Solutions, LLC <
> [email protected]> wrote:
>
> > On 2/1/2011 4:13 PM, Sytze de Boer wrote:
> > > Mike, I have encountered similar issues after clients "upgraded" to 2007
> > > I now "auto-save" the file back to 2003 before working with the file
> >
> >
> > Hi Sytze,
> >
> > I saved the file as XLS before I tried to work with it, but it still
> > failed with the same error.
> >
> > Any other ideas?
> >
> > --
> > Mike Babcock, MCP
> > MB Software Solutions, LLC
> > President, Chief Software Architect
> > http://mbsoftwaresolutions.com
> > http://fabmate.com
> > http://twitter.com/mbabcock16
> >
[excessive quoting removed by server]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
