VBA To Import Access data into Excel worksheet

[shelby55]

Hi

I am using MS-Office 2002.

I have an Excel document that is being used as a "dashboard". The data is coming from an Access table and I want to automate Excel so when it opens it pulls whatever the most current data is from Access.

When doing this I want the VBA code doesn't need to reference "hard coded" paths because I'm developing this to go on another computer. Note that both Excel and Access will be saved to the same folder.

When I use the macro to record querying the database I get this:

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=E:\My Documents\HIMS\HDH\Informatics\ED_PIP\DART_Auto\Test1_July 5 2010\dartData.mdb;DefaultDir=E:\M" _
), Array( _
"y Documents\HIMS\HDH\Informatics\ED_PIP\DART_Auto\Test1_July 5 2010;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("A3"))
.CommandText = Array( _
"SELECT DartData.Date, DartData.ED_Visits, DartData.ED_CTAS1, DartData.ED_CTAS2, DartData.ED_CTAS3, DartData.ED_CTAS4, DartData.ED_CTAS5, DartData.Count_LWBS, DartData.ED_Admits, DartData.ED_NonAdmit, " _
, _
"DartData.TPIA, DartData.Total_EDLOS, DartData.EDLOS_NonAdmit, DartData.CTAS12_NonAdmit, DartData.CTAS3_NonAdmit, DartData.CTAS45_NonAdmit, DartData.Count_CTAS12_NonAdmit_8hrs, DartData.Count_CTAS3_Non" _
, _
"Admit_8hrs, DartData.Count_CTAS45_NonAdmit_4hrs, DartData.EDLOS_Admits, DartData.CTAS12_Admit, DartData.CTAS3_Admit, DartData.CTAS45_Admit, DartData.Count_CTAS12_Admit_8hrs, DartData.Count_CTAS3_Admit" _
, _
"_8hrs, DartData.Count_CTAS45_Admit_4hrs, DartData.Count_IPNoBed, DartData.IPLOS_Dischg, DartData.Count_IPDischg, DartData.Count_IPDischgAll, DartData.Count_Dischg1100, DartData.Count_Dischg1400, DartD" _
, _
"ata.ALC, DartData.IPLOS_DischgUnit1, DartData.Count_IPDischgUnit1, DartData.Count_IPDischgUnit1All, DartData.Count_Dischg1100Unit1, DartData.Count_Dischg1400Unit1, DartData.ALCUnit1, DartData.IPLOS_Di" _
,,)
.Name = "Query from MS Access Database"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = False
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

Is there any way to also just pull in all columns of the table without mentioning each by name? Thanks.

 

[SkipVought]

Hi,

Select *
From.....

Also if you add this, ONE TIME, all you need do is REFRESH to get new data. No VBA required.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

HI Skip

Thanks so much! But how do I get it so that the "query refresh" dialog box doesn't show up each time I open the worksheet?

I realize I just have to select "enable automatic refresh" but I won't be the one using the tool. Thanks.

 

[SkipVought]

Use the Workbook_Open event.

Private Sub Workbook_Open()
  Sheets([i]YourSheetName[/i]).Querytables(1).Refresh false
End Sub

paste in the ThisWorkbook Code window, substituting your sheet name.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}