I have an Access 2003 database that imports an Excel 2003 spreadsheet from the user's desktop and names the table with the username. Here's my code:
Private Sub cmdRunReport_Click()
Dim stDocName As String
Dim strFile As String 'Desktop XLS
Dim strFindID As String
Dim UserName As String
' Assign username to a variable
UserName = Environ("USERNAME")
' Convert username to UPPERCASE
strFindID = StrConv([UserName], 1)
' Create spreadsheet name from a variable (LDM.xls)
strFile = strFindID & ".xls" 'strFile = Leigh.xls
' Create path to desktop spreadsheet using a variable (C:\Documents and Settings\LDM\Desktop\ldm.xls)
strFile = Environ("USERPROFILE") & "\Desktop\" & [strFile] 'C:\Documents and Settings\LDM\Desktop\ldm.xls
' If it existed, the previous table will be deleted
'Call the Function
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strFindID, strFile, True
stDocName = "Report1" ' Report1 is the name of the report
DoCmd.CopyObject , "TABLENAME", acTable, UserName
DoCmd.OpenReport stDocName, acPreview
'Delete the desktop spreadsheet
I have multiple reports and I'd like to be able to run the reports from the same table, however, the TABLENAME will vary depending on the user who is running the report.
Therefore, if I go to the Record Source of the report, I need to change TABLENAME to USERNAME.
Can I assign the Tablename to a variable and if so how?