Good afternoon,
I have a query setup in a database.
Is it possible to have a macro open up the query and dump the data into excel?
Good afternoon,
I have a query setup in a database.
Is it possible to have a macro open up the query and dump the data into excel?
Re: Open an access query xl
Why not run the query in Access and export the results to Excel?
Re: Open an access query xl
Hi Tanis,
The reason for my madness is I am very lazy!!
I managed to get some code to do what I want.
If anyone is interested.
Sub ShowErrors()
'On Error Resume Next
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stDB As String, stConn As String, stSQL As String
Dim wbBook As Workbook
Dim wsSheet1 As Worksheet
Dim Lrow As Long
Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String
Dim strSQL As String
'Instantiate the Connection objects.
Set cnt = New ADODB.Connection
Set wbBook = ActiveWorkbook
Set wsSheet1 = wbBook.Sheets("sheet1")
''''''''''''''''''''''''''''''''''''
''HardCode insert row1 values
Range("a1").Value = "Booking No"
Range("b1").Value = "Branch"
Range("c1").Value = "T/Sheet No"
Range("d1").Value = "Name"
Range("e1").Value = "Total Hours"
Range("f1").Value = "Branch"
Range("g1").Value = "Cost Code"
Range("h1").Value = "Pay Rate 1"
Range("i1").Value = "Hours1"
Range("j1").Value = "Pay Rate 2"
Range("k1").Value = "Hours2"
Range("L1").Value = "Pay Rate 3"
Range("m1").Value = "Hours3"
Range("n1").Value = "Total Pay"
Range("o1").Value = "NI"
Range("p1").Value = "Total Pay & NI"
Range("q1").Value = "WTR"
Range("r1").Value = "Agency Mark Up"
Range("s1").Value = "Expenses"
Range("t1").Value = "MGT Fee"
Range("u1").Value = "Total Net Invoice"
Range("v1").Value = "Suppliers VAT"
Range("w1").Value = "MGT Fee VAT"
Range("x1").Value = "Total VAT"
Range("y1").Value = "TOTAL INVOICE"
Range("z1").Value = "Agency"
Range("aa1").Value = "Job Title"
Range("ab1").Value = "Week Ending"
Range("ac1").Value = "Reporting To"
Range("ad1").Value = "Correct Cost Code"
'Range("ae1").Value = "Correct Cost Code"
''''''''''''''''''''''''''''''''''''''
'set the row for returning the recordset
Lrow = wsSheet1.Cells(65536, 1).End(xlUp).Row + 1
'Path to and the name of the database.
stDB = "G:\CTRDD\Db\CTRDD_be.mdb"
'Create the connectionstring.
stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & stDB & ";"
'Create the SQL-statement.
strSELECT = "SELECT tblCitiChecking.[Booking No], tblCitiChecking.[ Branch], tblCitiChecking.[T/Sheet No], tblCitiChecking.Name, tblCitiChecking.[Total Hours], tblCitiChecking.Branch, tblCitiChecking.[Cost Code], tblCitiChecking.[Pay Rate 1], tblCitiChecking.Hours1, tblCitiChecking.[Pay Rate 2], tblCitiChecking.[Hours 2], tblCitiChecking.[Pay Rate 3], tblCitiChecking.[Hours 3], tblCitiChecking.[Total Pay], tblCitiChecking.NI, tblCitiChecking.[Total Pay & NI], tblCitiChecking.WTR, tblCitiChecking.[Agency Mark Up], tblCitiChecking.Expenses, tblCitiChecking.[MGT Fee], tblCitiChecking.[Total Net Invoice], tblCitiChecking.[Suppliers VAT], tblCitiChecking.[MGT Fee VAT], tblCitiChecking.[Total VAT], tblCitiChecking.[TOTAL INVOICE], tblCitiChecking.Agency, tblCitiChecking.[Job Title], tblCitiChecking.[Week Ending], tblCitiChecking.[Reporting To], tblCitiChecking.CONCATENATE, tblBookingInformation.CostCode AS Correct"
strFROM = " FROM tblBookingInformation INNER JOIN tblCitiChecking ON tblBookingInformation.BookingNumber = tblCitiChecking.[Booking No]"
strWHERE = " WHERE (((tblBookingInformation.CostCode)<>[tblCitiChecking].[CONCATENATE]))"
strSQL = strSELECT & strFROM & strWHERE
Debug.Print strSQL
With cnt
.CursorLocation = adUseClient 'Necesary for creating disconnected recordset.
.Open stConn 'Open connection.
'Instantiate the Recordset object and execute the SQL-statement.
Set rst = .Execute(strSQL)
End With
'Copy the recordset to specified excel sheet & range.
With wsSheet1
.Cells(Lrow, 1).CopyFromRecordset rst
End With
cnt.Close
'Release objects from memory.
Set rst = Nothing
Set cnt = Nothing
End Sub
Display More
Don’t have an account yet? Register yourself now and be a part of our community!