Solved

MS Access: Macro Not Exporting to Excel

Posted on 2014-11-28
22
238 Views
Last Modified: 2014-11-30
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

0
Comment
Question by:dabug80
  • 8
  • 8
  • 3
  • +2
22 Comments
 
LVL 18

Expert Comment

by:SimonAdept
Comment Utility
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
 
LVL 1

Author Comment

by:dabug80
Comment Utility
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
 
LVL 18

Expert Comment

by:SimonAdept
Comment Utility
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
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
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
 
LVL 1

Author Comment

by:dabug80
Comment Utility
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
 
LVL 84
Comment Utility
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
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
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
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
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
 
LVL 18

Expert Comment

by:SimonAdept
Comment Utility
@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
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
@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
 
LVL 1

Author Comment

by:dabug80
Comment Utility
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 18

Expert Comment

by:SimonAdept
Comment Utility
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
 
LVL 1

Author Comment

by:dabug80
Comment Utility
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
 
LVL 18

Expert Comment

by:SimonAdept
Comment Utility
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
 
LVL 1

Author Comment

by:dabug80
Comment Utility
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
 
LVL 18

Expert Comment

by:SimonAdept
Comment Utility
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
 
LVL 1

Author Comment

by:dabug80
Comment Utility
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
 
LVL 18

Assisted Solution

by:SimonAdept
SimonAdept earned 100 total points
Comment Utility
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
 
LVL 1

Author Comment

by:dabug80
Comment Utility
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
 
LVL 18

Expert Comment

by:SimonAdept
Comment Utility
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
 
LVL 26

Accepted Solution

by:
Nick67 earned 400 total points
Comment Utility
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
 
LVL 1

Author Closing Comment

by:dabug80
Comment Utility
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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now