MS Access: Macro Not Exporting to Excel

Hello,

I have a query (qry_Patients-reminder), that I would like to export to the Excel sheet 'Exported_Data' of reminder.xlsm.  Currently the code below just opens up the workbook reminder.xlsm, but doesn't paste the data to the sheet. Am I missing something?

Here is my code:

Private Sub Command73_Click()

'This exports the reminder data to reminder.xlsm

Dim strFile As String
strFile = DLookup("path", "tblPath") & "\Reminder.xlsm"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_Patients-reminder", strFile, True, "Exported_Data"

Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
Set objWB = objXL.Workbooks.Open(strFile)

objXL.UserControl = True
Set objXL = Nothing

End Sub

Open in new window

LVL 1
dabug80Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SimonCommented:
You may be opening it too fast.

try adding a pause before you try to open the workbook.

sub ExampleKilltime
call killtime(0.5)'allow other events to occur for 0.5 seconds
end sub

Sub killTime(secs As Single, Optional DebugMode As Boolean = False, Optional silentMode As Boolean = True) 'Single precision value
    Dim Start As Single
    
    Start = Timer
    If DebugMode Then Debug.Print "killTime starting at " & Start
    Do
    'If debugMode Then Debug.Print Timer
    If Timer > Start + secs Then
        Exit Do
    Else
        DoEvents
    End If
    Loop
    If Not silentMode Then Debug.Print "Killed " & secs & " seconds."
    If DebugMode Then Debug.Print "killTime exiting at " & Timer
End Sub

Open in new window

0
dabug80Author Commented:
Ok, I added that code to the workbook - and increased the wait time to 5 seconds, but unfortunately the data still didn't export
0
SimonCommented:
Does the query return rows when executed within Access?
Does it work if you use a table as the source rather than a query? - you could temporarily change your query to a make-table query to test this.

This is an example working line from my production code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, LocalTableName, OutputFilename, True

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
My concern about the code is two-fold:

1. You are using "acSpreadsheetTypeExcel9" - that is Excel 2000 format, not Excel 2007. Try "acSpreadsheetTypeExcel12" instead.

2. You are saving as an xlsm file, and I've got a feeling that Microsoft doesn't allow that from Access. Try saving it as an xlsx file instead (at least for testing) and see if you have the same problem.

Does it actually create the file?
0
dabug80Author Commented:
Thanks Phillip,

I made the two changes and the data does export correctly to a .xlsx file. Do you have a recommendation for migrating it or referencing it in my .xlsm file?
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Have you tried using the xlsm extension after making the change to use the Excel12 constant? If I recall correctly, the xlsm format was not available in Excel 9 ...
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
From http://msdn2.microsoft.com/en-au/library/aa338205.aspx#office2007aboutnewfileformat_introduction, believe it or not, the xlsx format is identical to the xlsm format.

Therefore, save it as xlsx, and rename it xlsm before opening it.
0
Nick67Commented:
Sorry,

To me your code seems bass-ackward.
You're are doing the export and THEN instantiating Excel.
That can't be right, or if it is, prone to error.

It's not the way I'd attack it if the idea is to have Excel open at the end, anyway
.CopyFromRecordset is my friend

'Start a new workbook in Excel
Dim oApp As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim db as database
Dim rs as recordset

Set oBook = oApp.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
'get the data
Set db = CurrentDb
set rs = db.OpenRecordset("qry_Patients-reminder", dbOpenDynaSet, dbSeeChanges)

'Add the data starting at cell A1
oSheet.Range("A1").CopyFromRecordset rs

'Now save the file
Dim myfilename As String
myfilename = DLookup("path", "tblPath") & "\Reminder.xlsm"
oSheet.SaveAs (myfilename)


'Hand it over!
oApp.Visible = True
oApp.UserControl = True

'clean up
'Close the Database and Recordset
rs.Close
db.Close

Open in new window


I've never had a lot of joy trying to specify the exact format of the save.
Access 2003 gets angry trying to save to the new formats
You could try
oBook.SaveAs myfilename , 52 '= xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2013, xlsm)
0
SimonCommented:
@Nick. Nice, Every time I see .CopyFromRecordset I think 'must use that' but by the next time it's appropriate I've usually forgotten :)

Note that your code uses early binding so the Access DB needs to include a reference to Excel.
0
Nick67Commented:
@SimonAdept
Since I generally want to use VBA to apply formatting to the sheet, I want the sheet open, anyway
And CopyFromRecordset permits you to aim the insert to a row and cell.
Generally, I aim to A2
oSheet.Range("A2").CopyFromRecordset rs

Because I knock the FieldNames into row 1
'Add the field names in row 1
Dim i As Integer
Dim iNumCols As Integer
iNumCols = rs.Fields.count
For i = 1 To iNumCols
oSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
Next


Blow in the recordset
'Add the data starting at cell A2
oSheet.Range("A2").CopyFromRecordset rs


Format the columns for height and width
'Format the header row as bold and autofit the columns
With oSheet.Range("a1").Resize(1, iNumCols)
.Font.Bold = True
.EntireColumn.AutoFit
End With


And apply number formats where appropriate
oSheet.Range("D:D").NumberFormat = "dd-mmm-yy"
oSheet.Range("G:G").NumberFormat = "dd-mmm-yy"
oSheet.Range("H:T").NumberFormat = "$#,##0.00"


Beats the daylights out of TransferSpreadsheet in terms of versatility!
0
dabug80Author Commented:
Hi Nick,

Thanks for your suggestion. Being a newbie, I don't quite understand it. Is your code a VBA code to add in Access? But I need to have the Excel workbook open to use it?

Can I therefore edit the code to open the 'reminder.xlsm' workbook and then have access transfer the data with your macro?
0
SimonCommented:
Nick's code should go in your Access database. You don't have to have Excel open at the time. Your button in Access fires the code and the code creates a new instance of Excel to drop the data into.
0
dabug80Author Commented:
Ok, I took Nick's code and added it as the following new VBA code:

Private Sub mac_expertsexchangetest()

'Start a new workbook in Excel
Dim oApp As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim db As Database
Dim rs As Recordset

Set oBook = oApp.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
'get the data
Set db = CurrentDb
Set rs = db.OpenRecordset("qry_Patients-reminder", dbOpenDynaset, dbSeeChanges)

'Add the data starting at cell A1
oSheet.Range("A1").CopyFromRecordset rs

'Now save the file
Dim myfilename As String
myfilename = DLookup("path", "tblPath") & "\Reminder.xlsm"
oSheet.SaveAs (myfilename)


'Hand it over!
oApp.Visible = True
oApp.UserControl = True

'clean up
'Close the Database and Recordset
rs.Close
db.Close
End Sub

Open in new window


When running the code, I got the error on line 4:
Compile error: user-defined type not defined
0
SimonCommented:
Hi,

That's because your db doesn't have a reference to Excel

Note that your code uses early binding so the Access DB needs to include a reference to Excel.

Go to VBE/Tools/References and add check "Microsoft Excel"
or
Use the late-binding syntax that you had originally, which is
Set objXL = CreateObject("Excel.Application")
0
dabug80Author Commented:
Thanks. I've made the reference addition. I'm now getting the following error on line 14:

Run-time error 13: Type mismatch

Edit: I'm using Access 2007
0
SimonCommented:
Hmm. I got that too with Nick's code, but thought it was because I tried it in an existing database.

It's because it is expecting an ADO recordset rather than a DAO one.

Change this line
from
Dim rs As Recordset
to
Dim rs As DAO.Recordset
0
dabug80Author Commented:
Ok. I now get: Runtime error '1004':
This extension can not be used with the selected file type. Change the file extension in the File name text box or select a different file type by changing the Save as type.
0
SimonCommented:
Change this line... change "xlsm" to "xlsx"

myfilename = DLookup("path", "tblPath") & "\Reminder.xlsm"

I think this issue was referred to by Philip Burton earlier in the thread.
0
dabug80Author Commented:
Great. Thanks. The code does export the data. But there are a few problems:

1. The code creates a new version of the database reminder.xlsx. However I want Access to import the data into an existing spreadsheet (with the same name) that has other sheets

2. I want to be able to have macros in this workbook (hence the .xlsm extension). I can't have macros running in .xlsx
0
SimonCommented:
Ok, hold up. It seems the majority of this thread has been working on a different understanding.

You could change the code to open the existing workbook instead of adding one, then select the sheet and do the .copyfromrecordset method, but you'd also have to ensure that the sheet was blanked first, in case the query now returns fewer rows than when it was last run.

For your specific requirement, I'd have tended to do this from the Excel end without using any code at all.
1. Create a connection from Excel to the query in the Access database that contains the query.
2. set the properties of the connection to refresh when the workbook is opened.
https://support.office.com/en-gb/article/Connect-an-Access-database-to-your-workbook-a3d6500c-4bec-40ce-8cdf-fb4edb723525?ui=en-US&rs=en-GB&ad=GB

Edit: I'm not 100% sure that the connection can target a query as opposed to a table. May be necessary to run the query in Access as a MakeTable one and then target the results table from Excel.
0
Nick67Commented:
OK,

As SimonAdept says, it is easy enough to change the code to open an existing workbook, add a sheet and blow the data onto it.
It's simply a matter of changing the 'opening' statements, and you won't need a SaveAs, but a workbook save.
Dim oApp As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim myfilename As String
myfilename = DLookup("path", "tblPath") & "\Reminder.xlsm"

'open the existing workbook
Set oBook = oApp.Workbooks.Open(myfilename)
'add a sheet
Set oSheet = oBook.Worksheets.Add 'you can point where you'd like the new sheet to go, I haven't specified anything
'get the data
Set db = CurrentDb
Set rs = db.OpenRecordset("qry_Patients-reminder", dbOpenDynaset, dbSeeChanges)

'Add the data starting at cell A1
oSheet.Range("A1").CopyFromRecordset rs

'Now save the file
oBook.Save

'Hand it over!
oApp.Visible = True
oApp.UserControl = True

'clean up
'Close the Database and Recordset
rs.Close
db.Close

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dabug80Author Commented:
Perfect solution. Thanks so much for staying with me and adding valuable notes to the code. You're a star Nick.

Big thanks to Simon as well for helping me troubleshoot to a solution. Really appreciate it.

You've both delivered, when I was thinking about losing help and adopting a more manual go-between.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.