Prompt message box for importing access table data

[blanchard306]

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

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

[p45cal]

what was on this line before you changed it:

.CommandText = Array("NEED PROMPT HERE")

?

[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

[p45cal]

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?

[blanchard306]

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

[p45cal]

If the user knows the table name then test:

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!

[blanchard306]

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