Automate Excel with Query from Access

[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. I then need the worksheet to be e-mailed to certain people.

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

I posted in the wrong forum and had people query why I wouldn't just use "auto refresh" upon opening. I think the main reason is that I want to have the Excel worksheet update and then I want to e-mail. I haven't got the e-mail part figured out either but if anyone has any ideas on that I'd appreciate it.

 

[SkipVought]

Hi,

Will you ever need to change the connection to a different database or change the SQL code? If not, there is absolutely no reason at all, to use VBA. You can do it all from the sheet.

Skip,

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

 

[shelby55]

Hi Skip

Thanks for replying...no need to ever change database source or SQL code. But it does have to be fully automated for updates to the database (the Access query won't change but it will update daily) and after update I must be able to automatically have sent to e-mail recipients.

Thanks.

 

[kjv1611]

Also, as far as scheduling your workbook to run, is it running on a Windows machine that is always on, and the user is logged in? If so, you can easily schedule it via Windows Scheduler:

Start Menu - All Programs - Accessories - System Tools - Scheduled Tasks

I know I successfully used the Windows Scheduler for a few different tasks - some MS Office, and some other applications, that were server type applications that folks had been doing manually in the past. I said oh no, I aint promising I'm going to be in the same seat at the same time every stinking day to run a SIMPLE task that should be automated. So, I coded each task in each application - VBA for MS Office, PIC Basic for another mainframe based application, and probably another one, and just scheduled it all.

Oh my what we can accomplish when we really need to get it done, and won't personally take "no" for an answer.

Of course, if it's something that needs to be run daily at a scheduled time, if at all possible in a corporate environment, it'd be best carried out on a server, as most servers have a lot more redundancy in place - hard drives, power supplies, etc.. and more likely to have back-up power as well. Whereas if your user PC goes down, well, it's just down - most likely.

Well, hu, looks like I stepped on some box o' soap. I'll back off now.

[ROFL2]

 

[shelby55]

Hi KJV

Thanks for the response. Actually this will be on a server...does that make a difference? For instance, can I still use Windows Scheduler?

If I wanted to do as suggested by Skip, can it still be done if on a server?

Any suggestions for what code I need to auto e-mail the worksheet? Thanks!!

 

[SkipVought]

For instance, can I still use Windows Scheduler?

Unless you are running NO SECURITY, you cannot open Excel on a schedule and have a macro run in the workvook open event.

Skip,

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

 

[kjv1611]

What Skip means is that Unless you are running NO SECURITY in Microsoft Excel's Macro security settings, you cannot open Excel on a schedule and have a macro run in the workbook open event.

I don't recommend it for everyone, but for some folks, turning the macro security level down or off within Office applications is a no-brainer. But it really depends upon the user and the situation, of course.

In this case, it's not very likely that someone will be manually opening email attached spreadsheets on the server itself, at least I wouldn't expect so. So, unless there is some other good reason, I'd suggest it'd be fine to turn the macro security level to the lowest, and give the scheduling a test run.

But what I meant about the server, as well, is that there may be some better option altogether on a server. This sort of setup will work, but if there is a better alternative, of course, go with that.

 

[SkipVought]

In our company, I have no choice. Corporate IT dictates that security standard.

If you do have the choice, then you have to realize that changing the security setting will affect all workbooks.

Skip,

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

 

[kjv1611]

I'm surprised that I still have a choice on that one. I imagine that some day we won't have that choice, either. But even if there is a corporate policy on the security settings, for a server setup, the company could make an exception - not saying they will, b/c of course, it's up to them.

 

[shelby55]

Hi

Okay so Skip's previous response about using the events to code the required actions as the route to take - will that be affected by Macro security levels?

Thanks.

 

[SkipVought]

ANYTHING that uses VBA code, can only run if macros are enabled when the workbook opens. So either security is low, so no-one has the push the [ENABLE MACROS] button, or someone has to push the [ENABLE MACROS] button when the workbook opens.

Skip,

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