Importing Excel Spreadsheet Programmatically

[f15e]

I have created a module that will allow users to import an Excel spreadsheet as a table. The code in the module works fine and the spreadsheet is imported but when the data is imported into the Access table, the rows are out of order and there is a gap of about 70 rows between the data. See the pic below showing what I mean. The top pic is of the Excel spreadsheet that is imported into Access. The bottom pic is the imported data in Access after the import. As you can see the rows are out of order with the gap between data as previously mentioned.



Is there any way this can be fixed so additional formatting is NOT needed after the Excel import? I am trying to make the database as automated as possible so that the additional work is NOT needed behind the scenes (i.e. having to go into the table to adjust to the format needed, etc.) to make it work correctly. I would greatly appreciate any help or advice you provide. Thank you.

[andy49]

Can we see the code please?


Sent from my iPhone using Tapatalk

[f15e]

See code below:

************************************************** ***************

Private Sub Excel_Spreadsheet_Browse_Btn_Click()

Dim fileDiag As Office.FileDialog
Dim xl_file_path As Variant
Dim user_profile As String

Set fileDiag = Application.FileDialog(msoFileDialogFilePicker)

With fileDiag
.AllowMultiSelect = False
.Title = "Select File as Excel Spreadsheet .xls or .xlsx:"
.Filters.Clear
.Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx"

user_profile = LCase(Environ("UserName"))
.InitialFileName = "C:\Users" & user_profile & "\Desktop"
End With

' Check to see if user selected a file
If fileDiag.Show Then
For Each xl_file_path In fileDiag.SelectedItems
Me.Excel_FileName_txtbx = xl_file_path
Next
End If

End Sub

'************************************************* ***********************

Private Sub Import_Spreadsheet_Btn_Click()

Dim file_sys_obj As New FileSystemObject

' Check if user selected an Excel file to import
If Nz(Me.Excel_FileName_txtbx, "") = "" Then
MsgBox "An Excel file has not been chosen. Please select a file.", vbExclamation, "ERROR! FILE NOT SELECTED"
Exit Sub
End If

' Check if user entered a name for the table
If Nz(Me.Table_Name_Txtbx, "") = "" Then
MsgBox "A name for the table has not been entered. You must enter a name for the table before the spreadsheet can be imported.", vbExclamation, "ERROR! TABLE NAME NOT ENTERED"
Exit Sub
End If

' Check if user duplicates the name of a table
Call Check_Duplicate_Table_Name
If tableNameDuplicate = True Then
MsgBox "A table named " & "'" & Me.Table_Name_Txtbx & "'" & " already exists. Please enter a different table name.", vbExclamation, "ERROR! TABLE NAME DUPLICATED"
Exit Sub
End If

' Verify that Excel file exists (in the case where
' user pastes a file path into the file browser
If file_sys_obj.FileExists(Nz(Me.Excel_FileName_txtbx , "")) Then
Import_Proj_Data_Excel_Spreasheet.Import_Proj_Data _Spreasheet Me.Table_Name_Txtbx.Value, file_sys_obj.GetFileName(Me.Excel_FileName_txtbx)
Call Init_Form ' re-initializes my Form
Call ListTables ' updates the list of Tables located in my ListBox
MsgBox "The spreadsheet has been imported as table " & Me.Table_Name_Txtbx.Value & "."
Else
MsgBox "File not found! Verify the Excel spreadsheet file name and try again.", vbExclamation, "ERROR! FILE NOT FOUND"
End If

End Sub

'************************************************* ***************



The code below is located in Module named Import_Excel_Spreasheet

Public Sub Import_Proj_Data_Spreasheet(tableName As String, filePath As String)

Dim GetFileExt As String

GetFileExt = Right(fileName, Len(fileName) - InStrRev(fileName, "."))

If GetFileExt <> "xlsx" Or GetFileExt = "xls" Then
MsgBox "The file you tried to import was not of type Excel .xls or .xlsx.", vbExclamation, "ERROR! INCORRECT FILE FORMAT"
Else
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, tableName, filePath, True
End If

End Sub

[ssanfu]

To much code to work through tonight, but you do know that Access tables do not have an "Order". Access is not Excel - they are two completely different animals!
The blank records (not rows) are probably because there were blank rows (in Excel) that were imported.
Even though a table LOOKS like a spreadsheet, it is not.


To display records in an order, create/use a query.

To delete the blank records, run a delete query.
The pictures are extremely blurry, but it looks like the first field might be named "WT ITEM NO" (bad idea to use spaces in object names).
The delete query might look something like

DELETE * FROM Table1 WHERE Table1.[WT ITEM NO] Is Null;