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
> Output To Report To Excel Error, Access 2016    
 
   
lizba
post Jul 16 2017, 03:29 AM
Post#1



Posts: 236
Joined: 9-February 06
From: South Africa


Hi All
I am trying to export a report to excel with the aim to then to format it.
The code I am using is:



Dim rs As DAO.Recordset
Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim strFile As String

Set xl = New Excel.Application

xl.DisplayAlerts = False



strFile = "\\FTI-SERVER\Documents\Database Documents\Supplier Excel Schedules\" & Me.SuppliersSelect.Column(1) & ".ADV." & Format(Date, "yyyymmdd") & ".xls"

DoCmd.OutputTo acOutputReport, "SupplierExportAdv", acFormatXLS, strFile, True

'Start formatting

Set wb = xl.Workbooks.Open(strFile)
With wb.Sheets("SupplierExportAdv")
'xx
' formatting macro
'xx

End With
wb.Save
wb.Close
Set wb = Nothing

xl.Quit
Set xl = Nothing



The error I get is :

"Access cant save the output data to the file you have selected"

What am I doing wrong.

Regards
Liz
Go to the top of the page
 
lizba
post Jul 16 2017, 03:32 AM
Post#2



Posts: 236
Joined: 9-February 06
From: South Africa


Sorry guys, please ignore this, I have just found my rather silly error
hat_tip.gif
Go to the top of the page
 
WildBird
post Jul 16 2017, 07:04 PM
Post#3


UtterAccess VIP
Posts: 3,266
Joined: 19-August 03
From: Perth, Australia


"Sorry guys, please ignore this, I have just found my rather silly error"

Is this a Monday thing? :-)

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
DanielPineault
post Jul 17 2017, 07:08 AM
Post#4


UtterAccess VIP
Posts: 5,286
Joined: 30-June 11



You may like to grab the function found at https://www.devhut.net/2017/03/15/ms-access...rdset-to-excel/ (see: Taking Things Even Further :: ExportRecordset2XLS V2.0!) and then modify it further to suit your exact needs.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd October 2017 - 05:35 PM