How to pass string to a function in VBA?

Question

I am trying to pass different table names as strings so they can be used in a function that will export the table I specify to an excel spreadsheet. I cannot get it to work because when I try to do the "DoCmd.TransferSpreadsheet" command, it says that it does not recognize "table_name". I thought that if I passed a string name with the name of the table that it would work, but apparently it doesn't. Am I passing the string wrong, or am I not allowed to do this? Also, what type of excel spreadsheet should I export it as? I didn't see the differences between the different types.

Public Function NAME_FILE(table_name As String)

Dim strName As String
Dim strLocation As String
Dim strXLS As String
Dim strFinalName As String

strName = InputBox("What do you want to name the file?", "File Name")
strLocation = "C:\folder1\"
strXLS = ".xls"
strFinalName = strLocation & "" & strName & "" & strXLS

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "table_name", strFinalName, True

End Function

Public Function EXPORT_PRODUCT_CODE()
Dim T_PRODUCT_CODE As String

NAME_FILE (T_PRODUCT_CODE)

End Function

Public Function EXPORT_CAMPAIGN_CODE()
Dim T_CAMPAIGN_CODE As String

NAME_FILE (T_CAMPAIGN_CODE)
End Function

Regarding the choices for spreadsheet type, open the Access help topic for the DoCmd.TransferSpreadsheet Method and find the link which takes you to the AcSpreadSheetType Enumeration. — HansUp
– HansUp, Commented Jul 31, 2013 at 15:31

HansUp · Accepted Answer · 2013-07-31 15:16:36Z

3

table_name is the name of the parameter you're using to hold the name of a table. So, for TransferSpreadsheet, do not use quotes around that name. Then you will be feeding TransferSpreadsheet the name of the table instead of the literal text "table_name".

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
    table_name, strFinalName, True

IOW, you want to handle table_name the same as you did for your strFinalName variable --- don't enclose the name with quotes.

Also you have 2 other functions which call your NAME_FILE() function. Each of those creates a local string variable which is passed to NAME_FILE(). However you don't assign a table name to either of those variables, so the result is the same as NAME_FILE(""). Then, when you hit the TransferSpreadsheet statement, you have an empty string ("") for the TableName parameter which causes Access to complain "The action or method requires a Table Name argument."

edited Jul 31, 2013 at 15:16

answered Jul 31, 2013 at 14:55

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

nedstark179 Over a year ago

Thanks again for helping HansUp! I tried what you said, but I then got this error message "The action or method requires a Table Name argument."

HansUp Over a year ago

What do you see if you add MsgBox(table_name) to the NAME_FILE function?

HansUp Over a year ago

That will be a problem! Give the function the name of a table when you call it.

nedstark179 Over a year ago

I just got it to work. I did NAME_FILE("T_PRODUCT_CODE") in my EXPORT_PRODUCT_CODE function. Thanks again HansUp!!

Collectives™ on Stack Overflow

How to pass string to a function in VBA?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related