Automating Excel through Access and Importing Queries

[ztchnc]

Last week I started looking into automating Excel from Access so that I may be able to create spreadsheets faster. I was able to run a macro that created a new Excel sheet for me and made the layout I wanted however I am unfamiliar with pulling up queries and adding them to Excel. To get around this I recorded a macro in Excel and improted one of my queries, but suprisingly the macro created in Excel had syntax errors. I tried fixing and messing with it but I couldn't figure it out because I haven't done this before. So my question is how do I do this within Access without going into excel (if that is possible) and how do I just pull the value by itself without the table heading?

 

[PHV]

Have you considered the DoCmd.TransferSpreadsheet method ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[henniec]

Hi,

Try the following, it is an extract form one of my modules and I do this frequently. If you need more as to how to format Excel let me know.

Dim objXL As Object
Dim objWkb As Object
Dim objSht As Object
Dim DB As Database
Dim rs As Recordset
Dim intLastCol As Integer
Dim intLastRow As Integer
Dim strWhat As String, boolXl As Boolean
Dim mychart As Chart
Dim ActiveChart As Chart
Dim strRecords As String

intLastCol = 0
intLastRow = 0

Const conMAX_ROWS = 20000
Const conSHT_NAME = "Boiler 1 Data"
Const conWKB_NAME = “C:\DATA\Boiler pH Annual Chart.xls"

'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

'Check if Excel is running
If fIsAppRunning("Excel") Then
Set objXL = GetObject(, "Excel.Application")
boolXl = False
Else
Set objXL = CreateObject("Excel.Application")
boolXl = True
End If

Set DB = CurrentDb
Set rs = DB.OpenRecordset("qryBoilerpH1", dbOpenSnapshot)

'Calculate size of speadsheet
intLastCol = rs.Fields.Count '+ rs1.Fields.Count
If rs.RecordCount > 0 Then
rs.MoveLast: rs.MoveFirst
intLastRow = rs.RecordCount + 6

Set objWkb = GetObject(conWKB_NAME)
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'Transfer Boiler 1 information to Excel into worksheet "Boiler 1 Data"

With objXL
.Visible = True 'False

Set objWkb = .Workbooks.Open(conWKB_NAME)
Set objSht = objWkb.Worksheets("Boiler 1 Data")
objWkb.Worksheets("Boiler 1 Data").Activate

If Not Err.Number = 0 Then
Set objSht = objWkb.Worksheets.Add
objSht.name = "Boiler 1 Data"
End If

Err.Clear

On Error GoTo 0

With objSht


'Clear worksheet from previous data The additional values is to make sure there is nosupprises
.Range(.Cells(1, 1), .Cells(intLastRow + 800, _
intLastCol + 30)).ClearContents
.Range(.Cells(1, 1), .Cells(intLastRow + 100, _
intLastCol)).Font.Bold = False

'Copy data from the record set (query)

.Range("A2").CopyFromRecordset rs

'Add column headings
.Cells(1, 1).Value = "Sample Date "
.Cells(1, 2).Value = "pH"
.Cells(1, 3).Value = "pH 1 High"

 

[ztchnc]

PHV: I'm unfamiliar with that command can you elaborate?

henniec: I tried working with your code and it looks to me like it isn't finished and I kept getting errors with the line containing "conWKB_NAME" after I had placed my directory and file:

Const conWKB_NAME = “C:\Documents and Settings\~\My Documents\Book1.xls"

I thank you both for your assistance and I've kept looking at this problem further and I would like your feedback on the methods I'm trying.

1) I was also wondering if there is a way to automate the Tools Menu --> Office Links ->> [Analyze in Excel] of my Query because the Query within Access automatically updates itself so it has the most current info.

2) I have already attempted to transfer SQL strings between access and MS Query within Excel but that doesn't seem work.

3) I've only been successful in linking the query and creating a connection but that seems like what I would you use for my last resort.

4) I was able to use MS Query and select single cells to be filled and this seemed like a good idea. I didn't want to do this for every single field so I thought that if I could place it under a seperate function and pass in the destination cell and the field value/name it would be easier. Here is an example of the code I want to make a seperate function and send in (BBJX and "G4" for the cell):

' 'Find BBBJX and insert into field and undo changes to field
' With objXL.ActiveSheet.QueryTables.Add(Connection:= _
' "ODBC;DSN=MAX;DBQ=C:\DB\MAX\MAX.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
' , Destination:=objXL.Range("G4"))
' .CommandText = Array( _
' "SELECT `Query: QUICK`.BBBJX" & Chr(13) & "" & Chr(10) & "FROM `C:\DB\MAX\MAX`.`Query: QUICK` `Query: QUICK`" & Chr(13) & "" & Chr(10) & "GROUP BY `Query: QUICK`.BBBJX" _
' )
' .Name = "Query from MAX"
' .FieldNames = False
' .RowNumbers = False
' .FillAdjacentFormulas = False
' .PreserveFormatting = False
' .RefreshOnFileOpen = False
' .BackgroundQuery = False
' .RefreshStyle = xlOverwriteCells
' .SavePassword = True
' .SaveData = True
' .AdjustColumnWidth = False
' .RefreshPeriod = 0
' .PreserveColumnInfo = False
' .Refresh BackgroundQuery:=False
' End With
''' FOLLOWING WAS NECESSARY BECAUSE A MERGED CELL WOULD BE UNMERGED AND I HAD TO UNDO THE CHANGE (KNOW ANYWAY AROUND THIS?)
' objXL.Range("G4:I4").Select
' With objXL.Selection
' .HorizontalAlignment = xlCenter
' .VerticalAlignment = xlBottom
' .MergeCells = True
' End With

Thanks for being so helpful guys.

 

[PHV]

PHV: I'm unfamiliar with that command can you elaborate?
No <F2> nor <F1> key on your keyboard ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[henniec]

ztchnc,

The code I gave you work without any errors and as mentioned it is part of the complete code. This one copies data to three sheets and format there grahps.

What I would suggest to try and change/move your file to the c:\directory and so you don't have any sub-directories. If there is no errors you know where the problem lies.

I am not to familier with the setup you have in the following:

With objXL.ActiveSheet.QueryTables.Add(Connection:= _
' "ODBC;DSN=MAX;DBQ=C:\DB\MAX\MAX.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
' , Destination:=objXL.Range("G4"))
' .CommandText = Array( _
' "SELECT `Query: QUICK`.BBBJX" & Chr(13) & "" & Chr(10) & "FROM `C:\DB\MAX\MAX`.`Query: QUICK` `Query: QUICK`" & Chr(13) & "" & Chr(10) & "GROUP BY `Query: QUICK`.BBBJX" _
' )

Try and keep it simple. Declare a sql statement for your query. What I normally do for the sql statement is to ceate a query and copy the query-sql. It is easier to read and to manipulate. I find the above very difficult to read and it does not make any sence to me.

The above would be easier to handle if you declared the objXL and did your set statement for Excel, the workbook and the sheet.

It seems to me asif you have taken the Exel VB and copied it into Access. I do the same to help me overcome some problems but eliminate many of the formatting that come along. Use only that you want to change and ignore the other as it is most likely default.

I hope this helps.

Hennie