VBA Enhancement

[nancybrown]

Hi,

I converted an Access macro to VBA, I have a cmd button (cmdDD) on my form (frmDateDescription) to export this to a specific location. Can someone tell me please how to modify the code below in my Event Procedure of the cmd button to point to K:\CLE09\keyfound\Trust - Check Print for the export location.

Thank you in advance.

Private Sub DateDescription_Click()

DoCmd.OutputTo acOutputQuery, "qryCheckDateDescription", "ExcelWorkbook(*.xlsx)", True, "", , acExportQualityPrint



End Sub

 

[Micron]

What's curious is the syntax of the code line. You seem to be missing the output format (which normally means you'd get a prompt for it), so I'm kind of surprised it would work because when you omit optional parameter values but follow the omission with another parameter value, the missing parameter must be delimited with commas (as in where you have ,,). Saying that because ExcelWorkbook doesn't seem like a permitted value for the output format. So here's a link to the syntax - https://msdn.microsoft.com/en-us/vba/access-vba/articles/docmd-outputto-method-access
Perhaps all you're missing is the path. At least that link ought to show you where to put it.

 

[nancybrown]

Hi, what am I doing wrong . . . it's been a few years since working VBA . . . I keep getting a debug at the DoCmd line "run time 3170"


Private Sub cmdDD_Click()

Dim i As Integer
Dim myQueryName As String
Dim myExportFileName As String

myQueryName = "qryChecksbyDate" & Format(i, "00")
myExportFileName = "K:\CLE09\keyfound\Trust-Check Print" & Format(i, "00") & ".xlsx"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel10, myQueryName, myExportFileName, True

End Sub

 

[xenou]

I assume i will be zero since you never set it.
Is qryChecksbyDate00 the name of a query that exists in your database?

 

[nancybrown]

Please tell me what this field means that you are referring to.

 

[james_lankford]

Please tell me what this field means that you are referring to.

myQueryName = "qryChecksbyDate" & Format(i, "00")

he's saying i is never set to any value

so it will be zero

 

[Micron]

the acformat type parameter doesn't look right. I think this is a better link for the transferspreadsheet function since it contains a link to the acceptable types for the transfer
https://msdn.microsoft.com/en-us/vba/access-vba/articles/docmd-transferspreadsheet-method-access
I would expect the new error if the type doesn't exist. Follow the links and you'll see there is no such type unless you're using only the integer values for it.