Excel Macro Assistance - Query 1

[shelby55]

Hi

I am using Excel 2002 reporting from an Access 2002 database.

I am trying to automate an Excel document so that when data is sent to Access via another data source (patient ADT system) in CSV file then Excel will automatically requery the Access db.

The data has to fill starting from cell A3 to overwrite whatever is currently there. But with the current macro below the data is inserting to the left of the current columns instead.

Also, it's not saving the file as indicated in the macro - it is stopping at "activeworkbook.save".

Sub Import_Access()


Windows("DART_Auto.xls").Activate
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=E:\My Documents\HIMS\HDH\Informatics\ED_PIP\DART_Tests.mdb;DefaultDir=E:\My Documents\HIMS\HDH\Infor" _
), Array( _
"matics\ED_PIP;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;")), _
Destination:=Range("A3"))
.CommandText = Array( _
"SELECT qryDART.AdmDateCalc, qryDART.CountOfChartNo, qryDART.Tri1, qryDART.Tri2, qryDART.Tri3, qryDART.Tri4, qryDART.Tri5" & Chr(13) & "" & Chr(10) & "FROM `E:\My Documents\HIMS\HDH\Informatics\ED_PIP\DART_Tests`.qryDART qryDART" & Chr(13) & "" _
, "" & Chr(10) & "ORDER BY qryDART.AdmDateCalc")
.Name = "Query from MS Access Database_1"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = False
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
ActiveWorkbook.Save
'Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs Filename:="e:\DART.xls"
Application.DisplayAlerts = True
Application.OnTime TimeValue("15:30:00"), "Import_Access"

End With
End Sub

All assistance greatly appreciated. Thanks.

 

[kjv1611]

Is there a reason this is being done in a macro anyway? Why can't you simply import the table or query from the Access database, and have it auto-refresh upon opening? If you need it to return a certain set of data, you can build that query in Access, then import the query into Excel, and have it auto-refresh. That way, you can set it to wherever you want it to show on a worksheet, and it should stay put.

 

[kjv1611]

As far as the saving goes, is this worksheet ever opened by a person? If so, have it auto-update on open, and then have that person save it after it's opened... if that will work in your situation.

If you end up needing to keep with the VBA macro, then I'd suggest posting further over in the forum707, and mention there what is happening when the code stops - such as, are you getting any sort of error message..

And looking at it, I think I see the reason why the save operation isn't completing. You're trying to save a copy of the same workbook using the same name as what is already open - not possible. You need to just use ActiveWorkbook.Save rather than SaveCopyAs if you want to keep the same file name.

 

[SkipVought]

Unless you need to change the connection or the command text, from query to query, there's no reason at all to use VBA code.

Skip,

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

 

[shelby55]

Hi Skip

I replied in my other related e-mail on the VBA forum that no I won't need to change database, just be able to update it from Access (because Access will be updated daily) and I'll need to e-mail once the Excel sheet is updated.

Thanks.

 

[shelby55]

Hi

Sorry but I don't know if this matters but it must be automated for a certain time of the day i.e. the update and e-mail must occur at 7:00 a.m.

 

[SkipVought]

Sub Workbook_Open()
  ScheduleQuery
End Sub

Sub ScheduleQuery()
  application.ontime time(7,0,0), "RunQuery"
End Sub

Sub RunQuery()
   YourSheetObject.Querytables(1).Refresh False
   SendYourEmail
   ScheduleQuery
End Sub

Of source the SendYourEmail procedure would also need to be coded.


Skip,

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

 

[shelby55]

Thanks Skip!