I'm importing a Excel document into Access with some code.
Everything seemed to work well but, I notice I was missing some data between the Excel file and the table in Access.
The problem is that one of the columns (Budget Activity Program) in the Excel file has data that is both numbers and text.
When Access imports the spreadsheet, it imports the Budge Activity Program column as a number format.
When it does this, it deletes the entries that are a combination of characters that are both numbers and letters. See below.
If I select the Budget column in the Excel file and change the format from "general" to "text", then it imports just fine.
I would rather not have to do that step every time or rely on someone else to have to remember that step.
Is there some code that I could include to do this for me? See my code to import the file below.
Or possible another approach to prevent this entirely?
Below is the Excel file and Access Table after import that illustrates the problem.
………...
Here is the VBA code I use to import the Excel file.
Everything seemed to work well but, I notice I was missing some data between the Excel file and the table in Access.
The problem is that one of the columns (Budget Activity Program) in the Excel file has data that is both numbers and text.
When Access imports the spreadsheet, it imports the Budge Activity Program column as a number format.
When it does this, it deletes the entries that are a combination of characters that are both numbers and letters. See below.
If I select the Budget column in the Excel file and change the format from "general" to "text", then it imports just fine.
I would rather not have to do that step every time or rely on someone else to have to remember that step.
Is there some code that I could include to do this for me? See my code to import the file below.
Or possible another approach to prevent this entirely?
Below is the Excel file and Access Table after import that illustrates the problem.
Here is the VBA code I use to import the Excel file.
Code:
Private Sub Command12_Click()
Dim Msg1, Response1, Msg2, Response2, Style, Style2, Title, fName
Msg1 = "Are you sure you want to IMPORT the Allowance Status Report?"
Style1 = vbYesNo + vbCritical + vbDefaultButton2
Title = "IMPORT"
Response1 = MsgBox(Msg1, Style1, Title)
If Response1 = vbNo Then
Exit Sub
ElseIf Response1 = vbYes Then
On Error GoTo SubError
DoCmd.SetWarnings False
fName = "C:\Users\brweekley\Documents\Databases\Funding_Database\IHS - Allowance Status by Project and Location.xlsx"
DoCmd.OpenQuery "Delete_IHS - Allowance Status by Project and Location"
' True means the spreadsheet has column names for the first row
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "IHS - Allowance Status by Project and Location", _
fName, True
Msg2 = "Worksheet imported!"
Style2 = vbOKOnly + vbInformation
'Title = "IMPORT"
Response2 = MsgBox(Msg2, Style2, Title)
DoCmd.OpenQuery "UpdateImportTable_Q"
DoCmd.RefreshRecord
DoCmd.SetWarnings True
SubExit:
On Error Resume Next
Exit Sub
SubError:
MsgBox "Error Number: " & Err.Number & " = " & Err.Description, vbCritical + vbOKOnly, _
"An error occurred."
GoTo SubExit
End If
End Sub