Prompt message box for importing access table data

I have VBA that I use for importing Access queries into an Excel file but I am having trouble with making it prompt the user for the table name. Below is my code. Thanks

Code:

Application.DisplayAlerts = False With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _ "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=\\Prdhilfs03\l&i-sales&mkt\WORKAREA\Agencyservices\Accou" _ , _ "nting\AGY\Databases\DatabaseAudit.mdb;Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:" _ , _ "Registry Path="""";Jet OLEDB: Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB: Database Locking Mode=0;Jet OLEDB:Global Parti" _ , _ "al 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 OL" _ , _ "EDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False" _ ), Destination:=Range("$A$1")).QueryTable .CommandType = xlCmdTable .CommandText = Array("NEED PROMPT HERE") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = False .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .SourceDataFile = _ "\\Prdhilfs03\l&i-sales&mkt\WORKAREA\services\Accounting\AGY\Databases\DatabaseAudit.mdb" .ListObject.DisplayName = "Table_DatabaseAudit" .Refresh BackgroundQuery:=False End With Application.DisplayAlerts = True

what was on this line before you changed it:

Code:

.CommandText = Array("NEED PROMPT HERE")

?

It was the table name to be pulled from that database. The table name may change from week to week so this is why i'm trying to get it to prompt for the table name to pull. Thanks

Quote:

Originally Posted by blanchard306

It was the table name to be pulled from that database. The table name may change from week to week so this is why i'm trying to get it to prompt for the table name to pull. Thanks

Could you copy/paste the orginal line?

This is the full code unaltered. The line of code where I'm hoping to get a prompt instead of a hard set table is .CommandText = Array("01a_FinalTable")

Application.DisplayAlerts = False

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=\\Prdhilfs03\l&i-sales&mkt\WORKAREA\Agencyservices\Accou" _
, _
"nting\AGY\Databases\DatabaseAudit.mdb;Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:" _
, _
"Registry Path="""";Jet OLEDB: Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB: Database Locking Mode=0;Jet OLEDB:Global Parti" _
, _
"al 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 OL" _
, _
"EDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False" _
), Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdTable
.CommandText = Array("01a_FinalTable")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = _
"\\Prdhilfs03\l&i-sales&mkt\WORKAREA\services\Accounting\AGY\Databases\DatabaseAudit.mdb"
.ListObject.DisplayName = "Table_DatabaseAudit"
.Refresh BackgroundQuery:=False
End With
Application.DisplayAlerts = True

If the user knows the table name then test:

Code:

Static TName As String TName = InputBox("Enter table name", "Access Table name", TName) Application.DisplayAlerts = False With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array("OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=\\Prdhilfs03\l&i-sales&mkt\WORKAREA\Agencyservices\Accou", "nting\AGY\Databases\DatabaseAudit.mdb;Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:", "Registry Path="""";Jet OLEDB: Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB: Database Locking Mode=0;Jet OLEDB:Global Parti", "al 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 OL", "EDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False"), Destination:=Range("$A$1")).QueryTable .CommandType = xlCmdTable .CommandText = TName .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = False .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .SourceDataFile = "\\Prdhilfs03\l&i-sales&mkt\WORKAREA\services\Accounting\AGY\Databases\DatabaseAudit.mdb" .ListObject.DisplayName = "Table_DatabaseAudit" .Refresh BackgroundQuery:=False End With Application.DisplayAlerts = True

Additional/Changed code in red:

Static TName As String 'conventionally placed at top of sub.
TName = InputBox("Enter table name", "Access Table name", TName)
<snip>
.CommandType = xlCmdTable
.CommandText = TName
.RowNumbers = False
<snip>

If however, the user doesn't know what the table name might be, do you?

ps. Quite surprised by brevity of that .CommandText line, although .CommandType = xlCmdTable tends to explain it!

Thank you I'm going to test it out today!!!!