CODEWITHSUNDEEP
excelvbavisio
pouya
pouya

Reputation: 3766

viso 2 excel connection Err

I'm tring to connect to an excel file on my hard drive but i keep getting the following error

VBA error

Here is my code:

Public Sub excelConnection()

    Dim strConnection As String
    Dim strCommand As String
    Dim excelPath As String
    Dim vsoDataRecordset As Visio.DataRecordset
    excelPath = "G:\data\info.xlsx"
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" _
                    & "User ID=Admin;" _
                    & "Data Source=" + excelPath _
                    & "Mode=Read;" _
                    & "Extended Properties=""HDR=NO;IMEX=1;MaxScanRows=0;Excel 12.0;"";" _
                    & "Jet OLEDB:System database="""";" _
                    & "Jet OLEDB:Registry Path="""";" _
                    & "Jet OLEDB:Engine Type=37;" _
                    & "Jet OLEDB:Database Locking Mode=0;" _
                    & "Jet OLEDB:Global Partial Bulk Ops=2;" _
                    & "Jet OLEDB:Global Bulk Transactions=1;" _
                    & "Jet OLEDB:New Database Password="""";" _
                    & "Jet OLEDB:Create System Database=False;" _
                    & "Jet OLEDB:Encrypt Database=False;" _
                    & "Jet OLEDB: Don 't Copy Locale on Compact=False;" _
                    & "Jet OLEDB:Compact Without Replica Repair=False;" _
                    & "Jet OLEDB:SFP=False;" _
                    & "Jet OLEDB:Support Complex Data=False;" _
                    & "Jet OLEDB:Bypass UserInfo Validation=False;" _
                    & "Jet OLEDB:Limited DB Caching=False;" _
                    & "Jet OLEDB:Bypass ChoiceField Validation=False"

    strCommand = "select * from `straight-x-z$E3:F6`"
    Set vsoDataRecordset = ActiveDocument.DataRecordsets.Add(strConnection, strCommand, 0, "Points")

End Sub

Upvotes: 0

Views: 180

Answers (1)

USI
USI

Reputation: 11

I got mine working this way finally. here is my VBA code. Hope this helps. I used an Engine type 34

strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" + _
             "User ID=Admin;" + _
             "Data Source="+ srcLocation + _
             "Mode=Read;" + _
             "Extended Properties=""HDR=YES;IMEX=1;MaxScanRows=0;Excel 12.0;"";" + _
             "Jet OLEDB:Engine Type=34;"
    strCommand = "Select * from `SHEETNAME$`"
    Set vsoDataRecordset = Visio.ActiveDocument.DataRecordsets.Add(strConnection, strCommand, 0, "DATA")

Upvotes: 1

Related Questions