SendTQ2Excel function naming sheet

[Eljefegeneo]

I know this must have been beaten to death many times, but I cannot figure out how to name the Excel Workbook using the SendTQ2Excel function.
It comes out exactly how I want it; formatting is just great, but I cannot figure out the last part:

  [FONT=&quot]Public Function SendTQ2Excel(strTQName As String, Optional strSheetName As String)[/FONT]

Using SendTQ2Excel (myQry) gives me the desired results but names the workbook Book1.

I have tried the following with the results:
SendTQ2Excel (myQry, myfile) give me "Compile Error, Expected: ="
SendTQ2Excel myQry, myfile gives me "Compile Error, Syntax Error"
SendTQ2Excel (myQry, "ABCDE") gives me "Compile Error, Expected: ="
SendTQ2Excel (myQry, "ABCDE.XLsx") gives me "Compile Error, Expected: ="
SendTQ2Excel myQry, "ABCDE.xlsx" gives me "By Ref Argument type Mismatch"

I have researched this extensively and have tried just about all the variations to name the Workbook to no avail. Is there something else I should be doing?

[FONT=&quot]The function for those not familiar with it is on:[/FONT]
http://btabdevelopment.com/export-a-table-or-query-to-excel/

 

[Ranman256]

Use the TransferSpreadsheet command.
The sheet name is the last parameter.

 

[Galaxiom]

Both arguments are strings so must have double quotes around each of them.

Parentheses are required around the arguments of a function if it is used with a return value.

x = myfunction("y", "z")

If you use the function like a sub, discarding the return value, the sub syntax must be used. Hence no parentheses.

myfunction "y", "z"

 

[GinaWhipp]

The Functions needs the name of your query and the name of the Worksheet within the Workbook, not the Workbook name, i.e.

Call SendTQ2Excel("MyQuery", "WorksheetName")

 

[Galaxiom]

SendTQ2Excel ("MyQuery", "WorksheetName")

That syntax is incorrect. When discarding the return value, use either:

Call SendTQ2Excel("MyQuery", "WorksheetName")

or

SendTQ2Excel "MyQuery", "WorksheetName"

 

[GinaWhipp]

Oops, thanks Galaxiom, I did forget the *Call*

 

[Eljefegeneo]

I suppose I misread the function and it cannot name the Excel Workbook, only the Worksheet. And then I suppose that naming it means that I have saved it.
The bigger question is how do I get the result named as I want, i.e. "TransferBilling.xlsx" when it opens? Or rather, it would seem that I need to open it, save it (with the name I want) formatted the way I want it. For this I will have to do additional research.



Thanks to all.

 

[GinaWhipp]

Okay, so after you drop the data on to Sheet1 you want to save the Workbook. Something like...

xlWBk.SaveAs "TheDrive\YourFolder\NameOfYourFile.xlsx", 51

At the end should work.

 

[Eljefegeneo]

Thank you. I was a little confused at first as to where to put this last bit of code, but finally it occurred to me that it should go in the module and Voila!

Much appreciated.

 

[GinaWhipp]

Yeah, I probably should have mentioned the Module... glad it worked for you!