Give Access Query and Alias using VBA Code

[bconner]

I am using the DoCmd.TransferSpreadsheet function below and it works fine except it uses the QueryName to Name each Tab of the SpreadSheet. Is there a way to give the Query an Alias Name so I can have each Tab of the Spreadsheet Named something more meaningful?


Below is my code

Private Sub Command85_Click()
User = Environ$("USERNAME")
Rename(Test,Query,'3A Responded Raw Data Aetna')
 

On Error GoTo ErrorHandle

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data Aetna", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data BCBS", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data CHAMPVA", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data Client", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data Contracted", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data Humana", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data Medicaid", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data Medicaid FSC 1108", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data Medicare", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data Metcare", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data Motor Vehicle", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data None", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data Self Pay", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data Tricare", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data United Health Group", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data Workers Comp", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True
MsgBox ("Responded Report has Exported Succesfully")
 
End Sub

ErrorHandle:
MsgBox (Err.Number & " " & Err.Description)
Private Sub Command86_Click()
 
 
 

End Sub

 

[c_smithwick]

A solution and an issue. Your code as written jumps you out of the Sub on error. End Sub should ALWAYS be the final line of your Subroutine. The code below should do what your looking for:

Private Sub Command85_Click()
Dim User As String
Dim strAlias As String
User = Environ$("USERNAME")
Rename(Test,Query,'3A Responded Raw Data Aetna')
 
On Error GoTo ErrorHandle
strAlias = "X" 'build your alias name here, substituting it for X
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data Aetna", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True, strAlias
strAlias = "X" 'build your alias name here, substituting it for X
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data BCBS", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True, strAlias
strAlias = "X" 'build your alias name here, substituting it for X
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data CHAMPVA", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True, strAlias
strAlias = "X" 'build your alias name here, substituting it for X
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data Client", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True, strAlias
strAlias = "X" 'build your alias name here, substituting it for X
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data Contracted", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True, strAlias
strAlias = "X" 'build your alias name here, substituting it for X
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data Humana", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True, strAlias
strAlias = "X" 'build your alias name here, substituting it for X
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data Medicaid", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True, strAlias
strAlias = "X" 'build your alias name here, substituting it for X
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data Medicaid FSC 1108", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True, strAlias
strAlias = "X" 'build your alias name here, substituting it for X
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data Medicare", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True, strAlias
strAlias = "X" 'build your alias name here, substituting it for X
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data Metcare", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True, strAlias
strAlias = "X" 'build your alias name here, substituting it for X
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data Motor Vehicle", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True, strAlias
strAlias = "X" 'build your alias name here, substituting it for X
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data None", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True, strAlias
strAlias = "X" 'build your alias name here, substituting it for X
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data Self Pay", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True, strAlias
strAlias = "X" 'build your alias name here, substituting it for X
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data Tricare", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True, strAlias
strAlias = "X" 'build your alias name here, substituting it for X
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data United Health Group", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True, strAlias
strAlias = "X" 'build your alias name here, substituting it for X
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3A Responded Raw Data Workers Comp", "C:\Documents and Settings\" & User & "\" & "Desktop\" & "Responded.xls", True
MsgBox ("Responded Report has Exported Succesfully")
 
Exit Sub
ErrorHandle:
MsgBox (Err.Number & " " & Err.Description)
End Sub