ERROR 3011 when exporting access qry to excel to create chart

[gippsy]

Hello
I have a form with three txtboxes to query between dates, one to select Date1, other to select Date2, and another to type the value of a code.
When exporting the file I need the file name as indicated in line "sExcelWB=.... dd_mm_yyyy_plot_qry_task.xls

This is the code:

Sub cmdTransfer_Click()
Dim sExcelWB As String
Dim xl As Object ''Excel.Application
Dim wb As Object ''Excel.Workbook
Dim ws As Object ''Excel.Worksheet
Dim ch As Object ''Excel.Chart
Dim myRange As Object

Set xl = CreateObject("excel.application")

sExcelWB = "D:\testing\" & Replace(Me.txttask_from, "/", "_") & " - " & Replace(Me.txttask_to, "/", "_") & "_" & Replace(Me.txttask_plot, "/", "_") & "_qry_task.xls"

'This will overwrite any previous run of this query to this workbook

'I coloured on yellow the code line showing ERROR 3011
[COLOR="Yellow"]DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, sExcelWB, True[/COLOR]

Set wb = xl.Workbooks.Open(sExcelWB)


'Sheets are named with the Access query name
Set ws = wb.Sheets("qry_task")
Set ch = xl.Charts.Add
ch.ChartType = xlColumnClustered


xl.Visible = True
xl.UserControl = True

End Sub

With this form, I need to export the query to Excel and to create a chart.

Again as in former question in this Forum I VBA Editor displays an execution ERROR this time ERROR 3011. I have been, trying for hours to fix this error.
I am really confused by this Execution Errors.
I really appreciate if anyone can help me.

 

[Mark_]

First, after your line that sets sExcelWB add

MsgBox "Exporting to: " & sExcelWB

to see exactly what you are trying to write to.

Second, you will want to verify that Me.TxtTask_Plot does not have a "" in it as you are only searching for a "/". I'm not sure how this field is itself formatted but make sure you are ONLY using valid characters for a file name.

If everything checks out, then third make sure you have the proper permissions to the location you are writing to.

If it is none of the above let us know.

 

[MajP]

You may want to test the name without underscores and any non alphabetic characters. Seems to be lots of issues with more than one underscore in a name, spaces, periods.

 

[gippsy]

Mark
I really appreciate your reply

Well, I did your recommendation.
The MsgBox is "Exporting to: D:\testing\01_08_2018 * 03_08_2018_85_qry_task.xls"

I wrote the code using the Intellisense to check txt names.
When I run the code VBE displays same Error 3011
I am working on a personal computer.
I don't understand the meaning: "you have the proper permissions to the location you are writing to.

MajP
I tested the name without underscores and VBE

Many thanks, again

Again many thanks for your help

 

[Mark_]

Just to make sure, that "*" in your posted file name is really something else, yes? If not, remove it and one of the spaces. "*" is not a valid character in file names.

As to permissions, since you are referencing a "D:", I wasn't sure if that was another device or not.

You may also want to switch the format for your date portions to be YYYYMMDD so that they will sort properly when in file explorer. 20180801 will come after 20180102 but 02_01_2018 comes after 01_08_2018...

 

[arnelgp]

try to use Format instead of Replace:

sExcelWB = "D:\testing\" & Format(Me.txttask_from, "dd_mm_yyyy") & " - " & Format(Me.txttask_to, "dd_mm_yyyy") & "_" & Format(Me.txttask_plot, "000") & "_qry_task.xls"

 

[Cronk]

The problem is not so much in the name of the file to which the data is being exported, but what is to be exported.


The line in yellow in #1 (bad color by the way) is missing the argument containing the source paramater


Should be
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TheQueryWithDataBeingExported",sExcelWB, True

 

[gippsy]

Cronk.
You are right with the bad yellow colour, it is in my vba editor. I really apologize.
Your code works perfectly.
Thank you for your contribution.

I have been googling for a code to export the query and the chart in one Sheet. Could anybody suggest me how to do it? Do I have to write a new post?

Thank you very much indeed.

 

[Mark_]

Cronk, Good catch!

 

[arnelgp]

you can post a sample db and we can give you a sample code.

 

[Cronk]

I had a job which required something similar with the export of data to a spreadsheet and a bar chart based on that data.


Rather than trying to create the chart by automation, I prepared the chart in an excel template based on the cells in which the data was held.


In my case, the user could select a range of dates to display in the chart ie more or less rows (same number of columns) and I used automation only to change the data range.