Jeff, I have Windows 8.1 running with Office 2013 installed. I just tested the
following on an Excel 2003 spreadsheet and it worked. Hope it helps.
PROCEDURE Excel_ODBC
LPARAMETERS lcFile,lcXLTab,lcCursor
IF PCOUNT() = 0 OR TYPE("lcFile")<>"C"
RETURN
ENDIF
IF PCOUNT() = 1 AND !FILE(lcFile)
RETURN
ENDIF
IF PCOUNT() < 2 OR TYPE("lcXLTab")<>"C"
lcXLTab = ["Sheet1"]
ENDIF
IF PCOUNT() < 3 OR TYPE("lcCursor")<>"C"
lcCursor = "curXLODBC"
ENDIF
USE IN SELECT(lcCursor)
**Excel ODBC driver import into VFP9
*-----------------------------------
* AUTHOR: Trevor Hancock
* CREATED: 02/15/08 04:55:31 PM
* ABSTRACT: Code demonstrates how to connect to
* and extract data from an Excel 2007 Workbook
* using the "Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)"
* from the 2007 Office System Driver: Data Connectivity Components
*See article at: http://support.microsoft.com/kb/949529
*-----------------------------------
LOCAL lcXLBook AS STRING, lnSQLHand AS INTEGER, ;
lcSQLCmd AS STRING, lnSuccess AS INTEGER, ;
lcConnstr AS STRING
lcXLBook = lcFile
lcConnstr = [Driver=] + ;
[{Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};] + ;
[DBQ=] + lcXLBook &&+ [;] && trailing ; suggested by Fred
Taylor 10/21/2013, doesn't appear to be necessary
*-- Attempt a connection to the .XLSX WorkBook.
*-- NOTE: If the specified workbook is not found,
*-- it will be created by this driver! You cannot rely on a
*-- connection failure - it will never fail. Ergo, success
*-- is not checked here. Used FILE() instead.
lnSQLHand = SQLSTRINGCONNECT( lcConnstr )
**added 9/19/2012
IF lnSQLHand < 1
=MESSAGEBOX( "Sorry, I could not open the file using the
following options:"+CHR(13)+ ;
"lnSQLHand=
"+TRANSFORM(lnSQLHand)+CHR(13)+ ;
"param1=
lcFile= "+lcFile+CHR(13)+ ;
"param2=
lcXLTab= "+lcXLTab+CHR(13)+ ;
"param3=
lcCursor= "+lcCursor+CHR(13)+ ;
"lcConnstr=
"+lcConnstr , ;
0+16,"Connection error in Common.Excel_ODBC")
RETURN
ENDIF
*-- Connect successful if we are here. Extract data...
lcSQLCmd = [Select * FROM "]+ALLTRIM(lcXLTab)+[$"]
lnSuccess = SQLEXEC( lnSQLHand, lcSQLCmd, lcCursor )
IF lnSuccess < 0
LOCAL ARRAY laErr[1]
AERROR( laErr )
=MESSAGEBOX("Sorry, data was not returned from:"+ ;
CHR(13)+lcXLBook+ ;
CHR(13)+"Because of the following error:"+ ;
CHR(13)+laErr(3),0+16,'Excel ODBC error')
SQLDISCONNECT( lnSQLHand )
RETURN .F.
ENDIF
SQLDISCONNECT( lnSQLHand )
****End of Excel_ODBC
Customized Business Services, LLC (928) 580-6352
Dennis Schuette Primary:
[email protected]
350 West 16th Street, Suite 309 Alternate:
[email protected]
Yuma, AZ 85364 Map it
-----Original Message-----
From: ProfoxTech [mailto:[email protected]] On Behalf Of Stephen
Russell
Sent: Tuesday, April 08, 2014 10:59 AM
To: [email protected]
Subject: Re: [NF] Excel 2003 on Windows 8
On Tue, Apr 8, 2014 at 11:36 AM, Jeff Johnson <[email protected]> wrote:
>
>
>> Okay, I found VFP ODBC drivers and installed them. Now it works
>> with
> the below connection string with one slight problem
>
> lnsqlhandle = SQLSTRINGCONNECT([Provider=Microsoft.ACE.OLEDB.12.0;
> Data Source="Excel.dsn"; DBQ=] + lcspreadsheet + [; Extended
> Properties="Excel 8.0; HDR=YES";])
>
> The Data Source must be incorrect because a window pops up and says
> select data source. I select Excel.dsn and it works.
>
> <rant>I can't believe the amount of time spent getting things to work
> on Windows 8 that worked fine on Windows 7! ODBC, Remote Desktop,
> simple network connections. Sheesh!</rant>
>
>
------------------------
http://www.connectionstrings.com/excel/ for all the information on how to work
with connectionstrings & excel
--
Stephen Russell
Sr. Analyst
Ring Container Technology
Oakland TN
901.246-0159 cell
--- StripMime Report -- processed MIME parts --- multipart/alternative
text/plain (text body -- kept)
text/html
---
[excessive quoting removed by server]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.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.
