I am trying to use the code below to allow the user to select a spreadsheet and set a table on the 1st sheet to that source. Most of the code works, but because I took most of it from a recorded macro, I think there is a reference issue and I cannot figure it out. I am running this code from the click event on a button on the 2nd sheet in the workbook and the table is on the 1st sheet.

The 1st problem I encountered was with the 4 commented out lines that start with "Server". I couldn't figure them out so I commented them out. If these are important properties to set then I need to figure out how to.

The 2nd problem I have is with the last With statement (starting with "With Selection.ListObject.QueryTable"). This is where I think there is a reference problem.

Can someone help me out? This code actually does set the source to the selected file (FullFileName variable), so it almost works completely!

Thanks!


CODE:

Dim FullFileName As Variant

FullFileName = Application.GetOpenFilename("All Files (*.*),*.*", 1, "Custom Dialog Title", , False)

If FullFileName <> False Then

'Setting new table source.

With ActiveWorkbook.Connections("Raw_Quad_Data").OLEDBConnection
.BackgroundQuery = True
.CommandText = Array("Quad_View$raw_data")
.CommandType = xlCmdTable
.Connection = Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:\Documents and Settings\cghermaX\Desktop\Copy of Quad_" _
, _
"View_Tue 13-July-2010.xls;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry P" _
, _
"ath="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk O" _
, _
"ps=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:En" _
, _
"crypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=F" _
, "alse;Jet OLEDB:Support Complex Data=False")
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = FullFileName
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
' .ServerFillColor = False
' .ServerFontStyle = False
' .ServerNumberFormat = False
' .ServerTextColor = False
End With
With ActiveWorkbook.Connections("Raw_Quad_Data")
.Name = "Raw_Quad_Data"
.Description = ""
End With
ActiveWorkbook.Connections("Raw_Quad_Data").Refresh

With Selection.ListObject.QueryTable
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
End With