stonepreston
New Member
- Joined
- Jul 7, 2015
- Messages
- 2
I am trying to write some VBA that will import some data into a query table using a .odc file that is stored in a sharepoint data connection library. I used the macro recorder to record the process where I add the connection, then go to the existing connections and import the data into a table in the current worksheet(which worked when I did it manually).
The recorder spit out the following code (I removed the command text since it contains some sensitive info, but it was a big string of sharepoint related stuff like the list and view GUID):
However, when I run the macro to seemingly perform the exact same task that worked before I get the following error: Run time error 1004. I googled and it didnt really find anything that pertained to my use case
when I debug the following line is highlighted: .CommandType = 5
Any ideas on how to get this working?
The recorder spit out the following code (I removed the command text since it contains some sensitive info, but it was a big string of sharepoint related stuff like the list and view GUID):
Code:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">[COLOR=#00008B]Sub[/COLOR][COLOR=#000000] RecordedImportMacro[/COLOR][COLOR=#000000]()[/COLOR][COLOR=#000000]
Workbooks[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"MyWorkbook.xlsm"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]Connections[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]AddFromFile _
[/COLOR][COLOR=#800000]"http://path/to/my/odcfile/on/sharepoint.odc"[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#00008B]With[/COLOR][COLOR=#000000] ActiveSheet[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]ListObjects[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Add[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]SourceType[/COLOR][COLOR=#000000]:=[/COLOR][COLOR=#800000]0[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000] Source[/COLOR][COLOR=#000000]:=[/COLOR][COLOR=#000000] _
[/COLOR][COLOR=#800000]"OLEDB;Provider=Microsoft.Office.List.OLEDB.2.0;Data Source="""";ApplicationName=Excel;Version=12.0.0.0"[/COLOR][COLOR=#000000] _
[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000] Destination[/COLOR][COLOR=#000000]:=[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"$A$1"[/COLOR][COLOR=#000000])).[/COLOR][COLOR=#000000]QueryTable
[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]CommandType [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]5[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]CommandText [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]"some command text here"[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]RowNumbers [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]False[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]FillAdjacentFormulas [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]False[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]PreserveFormatting [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]True[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]RefreshOnFileOpen [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]False[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]BackgroundQuery [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]False[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]RefreshStyle [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] xlInsertDeleteCells
[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]SavePassword [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]False[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]SaveData [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]True[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]AdjustColumnWidth [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]True[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]RefreshPeriod [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]0[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]PreserveColumnInfo [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]True[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]SourceConnectionFile [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]"http://path/to/my/odcfile/on/sharepoint.odc"[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]ListObject[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]DisplayName [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]"My_Table"[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#00008B]With[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#00008B]Sub[/COLOR]</code>
However, when I run the macro to seemingly perform the exact same task that worked before I get the following error: Run time error 1004. I googled and it didnt really find anything that pertained to my use case
when I debug the following line is highlighted: .CommandType = 5
Any ideas on how to get this working?