UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Export Query To Excel Template, Access 2013    
 
   
ebroomhead
post Jan 20 2020, 04:11 PM
Post#1



Posts: 28
Joined: 19-May 15



I have done this before but the snippet I am using won't work. I get a compile error:syntax error on the highlighted line below and I have used Currentproject.path before with no problems. The Excel template is stored in the same directory as the MS Access program.

I am trying to download the data from query 11salesbycustgrp_detail to an Excel template called major customer sales master.xlsx and then save the Excel file as Major Customer Sales from XXXXXX to XXXXXX.

Any assistance would be much appreciated.

Code:

Dim rst
Dim XL As Object 'Excel.Application
Dim wb As Object
Dim vFile As Object

vFile = CurrentProject.Path & "major customer sales master.xlsx"

'//Create Objects
Set XL = CreateObject("excel.application")
Set rst = CurrentDb.OpenRecordset("11salesbycustgrp_detail")

'//Write Data in Excel workbook
set wb = XL.Workbooks.Open vFile
wb.Sheets("sheet1").Range("A2").CopyFromRecordset rst
wb.Savecopyas CurrentProject.Path & "Major Customer Sales " & "from " & Forms![main menu form]!txtstaper & " to " & Forms![main menu form]!txtendper & ".xlsx"

'//Close objects
wb.Close
XL.Quit

'//Cleanup object references (generally not necessary but good form)
Set wb = Nothing
Set rst = Nothing
Set XL = Nothing
End Sub
Go to the top of the page
 
MadPiet
post Jan 20 2020, 04:24 PM
Post#2



Posts: 3,478
Joined: 27-February 09



Maybe this will help?
http://theaccessweb.com/modules/mdl0035.htm
Go to the top of the page
 
GroverParkGeorge
post Jan 20 2020, 06:00 PM
Post#3


UA Admin
Posts: 36,778
Joined: 20-June 02
From: Newcastle, WA


One trouble shooting step I recommend is to place a DeBug.Print statement right after you create a variable. That way you can see what is actually in that variable when it is used.

CODE
vFile = CurrentProject.Path & "major customer sales master.xlsx"
Debug.Print vFile

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
WildBird
post Jan 20 2020, 06:22 PM
Post#4


UtterAccess VIP
Posts: 3,724
Joined: 19-August 03
From: Auckland, Little Australia


CODE
vFile = CurrentProject.Path & "\major customer sales master.xlsx"


Missing backslash.

I use a function,
CODE
Function CheckPath(ByVal strPath As String) As String
'Checks to see if a path has a backslash. If so, leaves it, otherwise appends one
If Right(strPath, 1) = "\" Then
    CheckPath = strPath
Else
    CheckPath = strPath & "\"
End If
End Function


Can always just use it for any path, and will append a backslash if it isnt there.

So I would use something like
CODE
dim strExportPath as string
dim strExportFile as string

strExportpath = CheckPath(CurrentProject.Path )
strExportFile = "major customer sales master.xlsx"

'I would then have code to check for the existence of this file, and delete it or something. Usually, I append a date and time stamp to the start of the filename, and then have another piece that gets the latest file from an export folder - but I digress.....

vFile = strExportPath & strExportFile


--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
cheekybuddha
post Jan 20 2020, 06:22 PM
Post#5


UtterAccess Moderator
Posts: 12,325
Joined: 6-December 03
From: Telegraph Hill


The Workbooks.Open() method is a function that returns a Workbook object and not a plain sub, so you must enclose its parameters in brackets.
CODE
set wb = XL.Workbooks.Open(vFile)


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
ebroomhead
post Jan 21 2020, 12:03 PM
Post#6



Posts: 28
Joined: 19-May 15



Thank you all for your input. Tried the Debug but it never triggered when running the code.

Added a backslash to the beginning of the Excel template name and put brackets around the vfile on the workbooks.open line and am now getting a Runtime error message 91, object variable or with block variable not set. Error seems to still be appearing on the vfile bolded line below.

Revised code:

Dim rst
Dim XL As Object 'Excel.Application
Dim wb As Object
Dim vFile As Object

vFile = CurrentProject.Path & "\" & "major customer sales master.xlsx"
'Debug.Print vFile
'//Create Objects
Set XL = CreateObject("excel.application")
Set rst = CurrentDb.OpenRecordset("11salesbycustgrp_detail")

'//Write Data in Excel workbook
Set wb = XL.Workbooks.Open(vFile)
wb.Sheets("sheet1").Range("A2").CopyFromRecordset rst
wb.Savecopyas CurrentProject.Path & "Major Customer Sales " & "from " & Forms![main menu form]!txtstaper & " to " & Forms![main menu form]!txtendper & ".xlsx"

'//Close objects
wb.Close
XL.Quit

'//Cleanup object references (generally not necessary but good form)
Set wb = Nothing
Set rst = Nothing
Set XL = Nothing
Go to the top of the page
 
GroverParkGeorge
post Jan 21 2020, 12:08 PM
Post#7


UA Admin
Posts: 36,778
Joined: 20-June 02
From: Newcastle, WA




'Debug.Print vFile

That line is commented out so it doesn't run.

Is that what you intended?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
cheekybuddha
post Jan 21 2020, 12:36 PM
Post#8


UtterAccess Moderator
Posts: 12,325
Joined: 6-December 03
From: Telegraph Hill


>> Dim vFile As Object <<

This should be:
CODE
' ...
  Dim vFile As String
' ...

Sorry, missed that first time round

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
ebroomhead
post Jan 22 2020, 11:14 AM
Post#9



Posts: 28
Joined: 19-May 15



I want to thank you all for your helpful comments and examples. notworthy.gif I managed to get it working the way that I wanted.

Here is the code in case anyone else uses snippets to assist in improving their MS Access programs.

Working Code: (note, highlighted items are particular to your own database)

Dim rst
Dim XL As Object 'Excel.Application
Dim wb As Object
Dim vfile As String

vfile = CurrentProject.Path & "\" & "major customer sales master.xlsx"


'//Create Objects
Set XL = CreateObject("excel.application")
Set rst = CurrentDb.OpenRecordset("tblmajcustsales")

'//Write Data in Excel workbook
Set wb = XL.Workbooks.Open(vfile)
wb.Sheets("sheet1").Range("A2").CopyFromRecordset rst
wb.Saveas FileName:=CurrentProject.Path & "\Major Customer Sales for " & Forms![main menu form]!txtstaper & " to " & Forms![main menu form]!txtendper & ".xlsx"

'//Close objects
wb.Close
XL.Quit

'//Cleanup object references (generally not necessary but good form)
Set wb = Nothing
Set rst = Nothing
Set XL = Nothing
MsgBox "Excel file creation for Major Customer Sales is complete.", vbOKOnly

Thanks again to all.
Go to the top of the page
 
WildBird
post Jan 22 2020, 04:33 PM
Post#10


UtterAccess VIP
Posts: 3,724
Joined: 19-August 03
From: Auckland, Little Australia


Good to see you got it working.
When you get the chance, you might want to review it, and see how you can make improvements.

Some basic things are

CODE
Dim rst as DAO.Recordset '( not declaring the type will make it a variant, taking up more memory etc)


Making it generic so you dont rely on hardcoded file names, so pass parameters with recordset name and filenames etc.

Add error handling

Add code to look at the files, in case they already exist, or are open for example.

Rename Sheet1 to something more meaningful.

Good luck with it, and if you need help with any of these concepts, let us know.



--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th February 2020 - 03:57 AM