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:


    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;"""


     


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


    Me.MousePointer = vbHourglass
        Set oReport = CreateObject("MailManagement.ReportGenerator")
        
        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;"""
        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
        End

  • Replied 4 August 2017, 2:48 pm EST

    I found the following connection strings:


           ODBC



    •  Standard:

      "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\MyExcel.xls;DefaultDir=c:\mypath;"

      TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.

  •  OLE DB



    •  Standard:

      "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
      "HDR=Yes;" indicates that the first row contains columnnames, not data

      "IMEX=1;" tells the driver to always read "intermixed" data columns as text

      TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.
Need extra support?

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

Learn More

Forum Channels