Running a macro in excel

  • Thread starter Thread starter Daniel
  • Start date Start date
D

Daniel

Hello,

There are two parts to this question:

1- How can I copy and then paste the results of a query into an excel
spreadsheet? Assuming the query is name "qry01" and the workbook is named
"Results" and I want to paste the results staring in cell "B5" of "Sheet1".

2-Assuming the excel spreadsheet contains a macro named "Ajust", how could I
then proceed to execute this macro after pasting the info?

Thank you very much for the help!

Daniel
 
Daniel,

This is relatively easy you have a few options here are 2 that I think are
the fastest

(1) Call the RecordSet.GetRows() method and then transpose the rusltant
variant array and then set an excel range object equal to the transpose
array, this method is by far the fastest when it comes to automation.

(2) scroll through you recordset and save it to a CSV file then automate
excel to open the CSV file.

The second part of your question is a matter of simply calling the "Run"
method of the Excel Application object. Just make sure that you macro is a
public subroutine.

Have fun!
Dan
 
Daniel

1) To get a specific set of data from Access into a specific Excel
spreadsheet I tend to create a recordset object containing the data and then
iterate through the records and fields. Assuming you are familiar with using
Excel as an object and using recordsets it might look something like this:

set wk = objExcel.workbooks("Result").Worksheets("Sheet1")
For lngRow = 0 to rs.RecordCount -1
For lngCol = 0 to rs.Fields.count -1
wk.cells(lngRow+1,lngCol+1) = rs.fields(lngCol).value
Next lngCol
Next lngRow

2) To run a macro in a workbook try the following:
objExcel.Application.Run "Results.xls!Ajust"
You might have to use the module name in which the macro is stored such as:
objExcel.Application.Run "Results.xls!ModuleName.Ajust"

An alternative you might want to consider is using the built in functions
for dumping data to Excel:

DoCmd.OutputTo acOutputQuery, "qry01", acFormatXLS, "C:\Path\Results.xls"

However, this will overwrite the Excel file each time you run it. You would
therefore need to store the macro in the PERSONAL.XLS macro and call it in
the same way:
objExcel.Application.Run "PERSONAL.XLS!ModuleName.Ajust"

I hope this helps - I'm not an MVP so wouldn't guarantee this is the best
solution but it works for me - I just noticed that nobody else had replied
yet!!!
 
Daniel - toy around with this .............

Put this junk in an Excel test workbook

Sub Main()
'This calls the function below
ImportAccessData
End Sub


Public Function ImportAccessData() As Boolean

On Error GoTo PROBLEM

'----------------------------------------
' Bring up Excel
' Hit Alt F11
'----------------------------------------
' GOTO -> Insert Menu - Add Module
' GOTO -> Tool Menu - References - MicroSoft ActiveX Data Objects 2.x
library
' the x will be 1 or 5 or 6 or 6 or 8 depending on what version of
MDAC you
' have installed
'----------------------------------------
Dim oCN As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim sSQLSelect As String
Dim oWS As Excel.Worksheet
Dim oRng As Excel.Range
Dim oRng1 As Excel.Range
Dim lngFldCnt As Long
Dim lngRecCnt As Long
Dim lng As Long

'-----------------------------
' [1] Connect to Database
'-----------------------------
Set oCN = Nothing
Set oCN = New ADODB.Connection
With oCN
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "c:\TestBed.mdb" 'Replace with your MDB
.Open
End With

'------------------
' fyi
'------------------
'MsgBox oCN.ConnectionString
'MsgBox oCN.Provider
'MsgBox oCN.Properties.Count
'MsgBox oCN.Properties(1).Name

'-----------------------------
' [2] Put your Select SQL Statement in a String Variable
'-----------------------------
sSQLSelect = "Select * from Table1"

'-----------------------------
' [3] Create a RecordSet Object - it will use
' both the Connection Object above and your SQL statement
'-----------------------------
Set oRS = Nothing
Set oRS = New ADODB.Recordset
With oRS
.ActiveConnection = oCN
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.Source = sSQLSelect '<-- Your SQL statement
.Open
End With

'-----------------------------
' [4] Get Excel ready
'-----------------------------
Set oWS = ThisWorkbook.Sheets(1)
Set oRng = oWS.Range("B5")
Set oRng1 = oRng.CurrentRegion
oRng1.ClearContents

'-----------------------------
' [5] Write Headings to Excel
'-----------------------------
lngFldCnt = oRS.Fields.Count
For lng = 0 To lngFldCnt - 1
oRng.Cells(1, lng + 1) = oRS.Fields(lng).Name
Next

'-----------------------------
' [6] Write Data under Headings
'-----------------------------
Set oRng = oRng.Offset(1, 0)
oRng.CopyFromRecordset oRS

'-----------------------------
' Kill objects
'-----------------------------
oRS.Close
Set oRS = Nothing
oCN.Close
Set oCN = Nothing

ImportAccessData = True
Exit Function

PROBLEM:
MsgBox Err.Number & " " & Err.Description, vbCritical,
"Function:ImportAccessData()"
ImportAccessData = False
Exit Function

End Function

John
 
Daniel said:
How can I copy and then paste the results of a query into an excel
spreadsheet? Assuming the query is name "qry01" and the workbook is named
"Results" and I want to paste the results staring in cell "B5" of "Sheet1".
Click to expand...

More details are required. Before the export, are there any column
headers in the workbook for the target data? If no, do you want column
headers with the results and should they be in row 4 or row 5? Is
there any data in the workbook in the target range? If yes, should
existing data be preserved or replaced?

Jamie.

--
 
I want the header to be pasted row 4 and the data in 5 and on. There may be
data on the worksheet, it should be cleared and the new data then pasted.

Daniel
 
Back
Top