Paste query to Excel template

[guinness]

Hi

With loads of help from vbaInet I was able to create a form with lots of conditional formatting that did pretty much everything I wanted it to do. The only problem is that it takes about 4 full minutes for the form to open.

As an experiment I am reluctantly now trying to display the results in Excel. I have created a template xls sheet and all I want to do is, on the press of a button, copy the results from my query and paste them in to cell a1 of my spreadsheet.

I found the following code online which I am trying to adapt.

 Private Sub update_tracker_Click()
Dim XL As Excel.Application
Dim wbTarget As Workbook
 Dim qdfResults As QueryDef
Dim rsResults As Recordset
 'Set up refernce to the query to export
 Set qdfResults = CurrentDb.QueryDefs("2014 Resources")
qdfResults.Parameters("Forms!2014 Resources") = Forms!2014 Resources
 
'Execute Query
Set rsResults = qdfResults.OpenRecordset()
 
'reference excel
Set XL = CreateObject("Excel.Application")
'refernce workbook
Set wbTarget = XL.Workbooks.Open("I:\Documents\Access files\skeleton db\2014 Resources.xlsx")
 'clear excel sheet
wbTarget.Worksheets("_2014_Resources").Cells.ClearContents
 'paste data from query to worksheet
wbTarget.Worksheets("_2014_Resources").Cells(1, 1).CopyFromRecordSet rsResults
wbTarget.SaveAs "I:\Documents\Access files\skeleton db\2014 Resources.xlsx", xlOpenXMLWorkbookMacroEnabled
wbTarget.Close
Set wbTarget = Nothing
XL.Quit
  
 End Sub

My limited knowledge however results in a couple of errors.

The first error User defined type not defined error appears at the very first line of my code

 Dim XL As Excel.Application

The next error occurs as I am not sure how to reference the query to export. The query is called 2014 Resources and outputs also to a form called 2014 Resources.

Can anyone help?

 

[vbaInet]

The first error User defined type not defined error appears at the very first line of my code

 Dim XL As Excel.Application

Add the reference to the Excel library:
1. In the code window click Tools > References
2. Select Microsoft Excel X.X Object Library (where X stands for the version of Excel you have installed - you'll see in the list).
3. Hit OK

Here's some code on how to transfer data from Access to an Excel spreadsheet. There are other examples on that site too:
http://www.btabdevelopment.com/ts/exportformrecordset

 

[guinness]

I also tried to adapt this that I found on here but still get User defined type not defined.

 Private Sub update_tracker_Click()
Dim XLApp As Excel.Application
Dim XLSheet As Excel.Worksheet
Dim tmpRS As DAO.Recordset
 Set XLApp = CreateObject("Excel.Application")
XLApp.Workbooks.Open "I:\Documents\Access files\skeleton db\2014 Resources.xlsx", True, False
XLApp.Visible = True
 Set XLSheet = XLApp.Workbooks.Sheets("_2014_Resources")
Set tmpRS = CurrentDb.OpenRecordset("2014 Resources")
 XLSheet.Range("A2").CopyFromRecordset tmpRS
 XLApp.Workbooks.Save
XLApp.Quit
tmpRS.Close
 Set tmpRS = Nothing
Set XLApp = Nothing
  
 End Sub

 

[vbaInet]

... I was able to create a form with lots of conditional formatting ... it takes about 4 full minutes for the form to open.

You should probably tell us how many conditional formats you have and how many text boxes you're applying it on

 

[guinness]

Hello again vbaInet.

References is greyed out in the tools menu

 

[guinness]

You should probably tell us how many conditional formats you have and how many text boxes you're applying it on

I probably shouldn't
About 5 format conditions being applied to approximately 250 text boxes. Can't think why that would be an issue for Access but not for excel??????

 

[vbaInet]

Hello!

You're still debugging your code. You need to hit the Stop button.

Well that's because they're made for different purposes. Excel was optimised to handle all that nice formatting. And the way data is fetched is totally different.

 

[guinness]

Ok

Got references sorted

Now I'm getting an error with this line of the second code I posted

Set XLSheet = XLApp.Workbooks.Sheets("_2014_Resources")

It highlights the word Sheets and the error is Method or data member not found.

 

[vbaInet]

Are you using the code in the link?

 

[vbaInet]

Study the codes in the link before posting. There are several ways of doing it and all are explained on that website.

 

[guinness]

Ok so I updated this code posted by Paul Eugen in response to a similar query.

It basically copies everything from my query "2014 Resources" to my spread sheet also named 2014 Resources. The spread sheet was created by exporting the query in the first place and then setting all sorts of conditional formatting.

 Private Sub update_tracker_Click()
 
Dim XLApp As Excel.Application
Dim XLSheet As Excel.Worksheet
Dim tmpRS As DAO.Recordset
 Set XLApp = CreateObject("Excel.Application")
XLApp.Workbooks.Open "I:\Documents\Access files\skeleton db\2014 Resources.xlsx", True, False
XLApp.Visible = True
 Set XLSheet = XLApp.Worksheets("_2014_Resources")
Set tmpRS = CurrentDb.OpenRecordset("2014 Resources")
 XLSheet.Range("A2").CopyFromRecordset tmpRS
 tmpRS.Close
 Set tmpRS = Nothing
Set XLApp = Nothing
 End Sub

Unlike the original code this opens the spreadsheet, pastes the data but does not save and close the spreadsheet as my end user will want to view the spreadsheet.

Earlier vbaInet (who must hate me by now) posted a link to a site documenting something similar. In their code they created a new spreadsheet. I mention this because I should always listen to vbaInet as, if I had in the first place I could have saved us both a lot of heartache and pain and also because the site linked to looks like something that should live in my favorites for some time.

Thanks guys

Guinness

 

[vbaInet]

Let me tidy up that code for you. I've added/removed some bits and moved some lines to their appropriate places:

Private Sub update_tracker_Click()
 
    Dim XLApp   As Excel.Application
    Dim XLSheet As Excel.Worksheet
    Dim tmpRS   As DAO.Recordset
    Dim strFile As String
    
    Set XLApp = New Excel.Application
    
    strFile = "I:\Documents\Access files\skeleton db\2014 Resources.xlsx"
    
    XLApp.Workbooks.Open strFile, True, False
    
    Set XLSheet = XLApp.Worksheets("_2014_Resources")
    Set tmpRS = CurrentDb.OpenRecordset("2014 Resources")
 
    XLSheet.Range("A2").CopyFromRecordset tmpRS
    
    XLApp.Visible = True
    XLApp.UserControl = True
    
    tmpRS.Close
    Set tmpRS = Nothing
    Set XLApp = Nothing

End Sub

... vbaInet (who must hate me by now...

Earlier vbaInet (who must hate me by now) posted a link to a site documenting something similar. In their code they created a new spreadsheet. I mention this because I should always listen to vbaInet as, if I had in the first place I could have saved us both a lot of heartache and pain and also because the site linked to looks like something that should live in my favorites for some time.

Thanks guys

Guinness

Not all is wasted. At least you learnt how to apply conditional formatting in code in Access and I suppose you now know better which application is best for displaying and formatting certain types of data.

Basically:
1. Short (rows) and wide (columns) - Excel
2. Tall (rows/records) and narrow (columns/fields) - Access