none
Opening an OLEDB connection - macro fails to run post recording RRS feed

  • Question

  • Hi folks,

    I recorded a macro to load an OLEDB connection file and open it within an excel workbook. The operation was successful in the first instance. Subsequent attempts to run the macro fail with a useless 1004 error that gives no clue as to the issue.

    .CommandType = 5 is where it blows up. Please help. Thanks

    Sub Macro1() ' ' Macro1 Macro ' ' Workbooks("Test.xlsm").Connections.AddFromFile _ "https://mysite.com:12345/ODC Connections/My Connection.odc" With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB;Provider=Microsoft.Office.List.OLEDB.2.0;Data Source="""";ApplicationName=Excel;Version=12.0.0.0" _ , Destination:=Range("$A$1")).QueryTable .CommandType = 5 .CommandText = Array( _ "<LIST><VIEWGUID>{865C2F0B-86AC-4CFB-A2B6-ABC257486528}</VIEWGUID><LISTNAME>{06E6DF56-4C28-45F8-AC8F-780D9D655B7B}</" _ , _ "LISTNAME><LISTWEB>https://mysite.com:18230/XX/_vti_bin</LISTWEB><LISTSUBWEB></LISTSUBWEB><ROOTFOLD" _ , "ER>/BT/Lists/MyList</ROOTFOLDER></LIST>") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .SourceConnectionFile = _ "https://mysite.com:12345/ODC Connections/My Connection.odc" .ListObject.DisplayName = "Table_MyConnection" .Refresh BackgroundQuery:=False End With End Sub

    Wednesday, March 9, 2016 2:22 PM

Answers

  • http://stackoverflow.com/questions/31273161/how-to-import-data-using-odc-file-in-excel-2013-vba 

    Thanks to Preston Stone for providing the answer.

    • Marked as answer by DeadManShoes Wednesday, March 9, 2016 3:55 PM
    Wednesday, March 9, 2016 3:55 PM

All replies

  • http://stackoverflow.com/questions/31273161/how-to-import-data-using-odc-file-in-excel-2013-vba 

    Thanks to Preston Stone for providing the answer.

    • Marked as answer by DeadManShoes Wednesday, March 9, 2016 3:55 PM
    Wednesday, March 9, 2016 3:55 PM
  • Thanks for sharing this information.

    Have a good day.

    Tuesday, March 22, 2016 3:16 AM