CODEWITHSUNDEEP
functionms-accessvba
nedstark179
nedstark179

Reputation: 572

How to pass string to a function in VBA?

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

Upvotes: 1

Views: 22937

Answers (1)

HansUp
HansUp

Reputation: 97101

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."

Upvotes: 3

Related Questions