converting from foxpro - .dbf to excell

Posted by: kmac303 on 4 August 2017, 2:48 pm EST

  • Posted 4 August 2017, 2:48 pm EST

    Is there any easy solution to connecting to an excel spreadsheet rather than a foxpro .dbf file?? Is it just a matter of changing the sourcetype to excel and taking out the driver???

    Connection string on the web:

    <FONT color=#ff0000>oReport.Connect = "Provider=MSDASQL.1;Extended Properties=""Driver={Microsoft Visual FoxPro Driver};UID=;PWD=;SourceDB=C:\dir\dirl\dir\Data;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=;Null=Yes;Deleted=Yes;"""</FONT>


    A button click on my test form. This is my class main function:

    Me.MousePointer = vbHourglass
        Set oReport = CreateObject("MailManagement.ReportGenerator")
    <FONT color=#ff0000>    oReport.Connect = "Provider=MSDASQL.1;Extended Properties=""Driver={Microsoft Visual FoxPro Driver};UID=;PWD=;SourceDB=C:\dir\dir\Data;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=;Null=Yes;Deleted=Yes;"""
    </FONT>    oReport.Source = "TEST_DATA"
        oReport.FilePath = App.Path
        strPath = oReport.GeneralRpts(1)
        Set oFileScripting = CreateObject("Scripting.FileSystemObject")
        If oFileScripting.FileExists(strPath) = True Then
            vntstream = oReport.ReadBinFile(strPath)
            r = StartDoc(strPath)
            Set oReport = Nothing
            If IsObject(oFileScripting) Then Set oFileScripting = Nothing
        End If
        Me.MousePointer = vbDefault

  • Replied 4 August 2017, 2:48 pm EST

    I found the following connection strings:


    • <B> Standard:</B>

      <DIV>"Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\MyExcel.xls;DefaultDir=c:\mypath;" </DIV>
      <BLOCKQUOTE><I>TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.</I></BLOCKQUOTE>
    <SPAN class=seclvl>
  •  OLE DB </SPAN>

    <SPAN id=exceloledb>

    • <B> Standard:</B>

      <DIV>"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""</B>"
      <BLOCKQUOTE><I>"HDR=Yes;" indicates that the first row contains columnnames, not data</I></BLOCKQUOTE>
      <BLOCKQUOTE><I>"IMEX=1;" tells the driver to always read "intermixed" data columns as text</I></BLOCKQUOTE>
      <BLOCKQUOTE><I>TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.</I></BLOCKQUOTE></DIV>
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels