Import Excel into Access Table (1 Viewer)

rmoreno

Registered User.
Local time
Today, 00:45
Joined
Aug 26, 2005
Messages
20
I am having a bit of trouble importing an Excel file. The file has dates which are converted into a string of numbers when they are imported into my table. Whenever I tried to change the field type to Date/Time I would lose all the data within that column. I also tried saving the spreadsheet into a CSV file before importing into Access which seems to work out, but I need to use code to make the process less operator dependent. This is what I would like to do:

1. from Access call up an API dialog box to allow for the user to navigate to the spreadsheet location. (This can vary)

2. Import the spreadsheet data deleting all empty rows and columns. Currently I'm doing this manually in the table.

3. Covert the spreadsheet to a CSV format

4. Import into an Access fixed table name (deleting any existing data within the table).

NOTE: the only reason for the CSV conversion is so that the date data will come into the table as a date and not a number. If the importing process can perform the input without the CSV conversion then this would be one less step to perform.

Attached is an example of the spreadsheet that I have been working with.

Any ideas?
 

Attachments

  • TestResults_mmddyy.zip
    19.1 KB · Views: 2,043

nshah1013

Registered User.
Local time
Yesterday, 22:45
Joined
Aug 24, 2005
Messages
13
First Create a form named Form and then create a command button called Command0

Then go to the code for the form and paste the following code. the only thing you will need to change is the dir in the bold line.

Hope this helps.

Option Explicit

Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Private Sub Command0_Click()
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String
Dim WrksheetName As String
Dim i As Integer
Dim oApp As Object

OpenFile.lStructSize = Len(OpenFile)
OpenFile.hwndOwner = Form.hwnd
'OpenFile.hInstance = App.hInstance
sFilter = "acSpreadsheetTypeExcel8 (*.xls)" & Chr(0) & "*.xls" & Chr(0)
OpenFile.lpstrFilter = sFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = "E:\"
OpenFile.lpstrTitle = "Use the Comdlg API not the OCX"
OpenFile.flags = 0
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
MsgBox "The User pressed the Cancel Button"
Else
MsgBox "The user Chose " & Trim(OpenFile.lpstrFile)
End If
Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
oApp.Workbooks.Open OpenFile.lpstrFile
With oApp
.Visible = True
With .Workbooks(.Workbooks.Count)
For i = 1 To .Worksheets.Count
WrksheetName = .Worksheets(i).Name
DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel97, WrksheetName, OpenFile.lpstrFile, False
Next i
End With

End With
Set oApp = Nothing
End Sub
 

nshah1013

Registered User.
Local time
Yesterday, 22:45
Joined
Aug 24, 2005
Messages
13
First Create a form named Form and then create a command button called Command0

Then go to the code for the form and paste the following code. the only thing you will need to change is the dir in the bold line. If you need the headings you can keep the True in the Transferspreadsheet else you can change it to False

Hope this helps.

Option Explicit

Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Private Sub Command0_Click()
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String
Dim WrksheetName As String
Dim i As Integer
Dim oApp As Object

OpenFile.lStructSize = Len(OpenFile)
OpenFile.hwndOwner = Form.hwnd
'OpenFile.hInstance = App.hInstance
sFilter = "acSpreadsheetTypeExcel8 (*.xls)" & Chr(0) & "*.xls" & Chr(0)
OpenFile.lpstrFilter = sFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = "E:\"
OpenFile.lpstrTitle = "Use the Comdlg API not the OCX"
OpenFile.flags = 0
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
MsgBox "The User pressed the Cancel Button"
Else
MsgBox "The user Chose " & Trim(OpenFile.lpstrFile)
End If
Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
oApp.Workbooks.Open OpenFile.lpstrFile
With oApp
.Visible = True
With .Workbooks(.Workbooks.Count)
For i = 1 To .Worksheets.Count
WrksheetName = .Worksheets(i).Name
DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel97, WrksheetName, OpenFile.lpstrFile, True
Next i
End With

End With
Set oApp = Nothing
End Sub
 

rmoreno

Registered User.
Local time
Today, 00:45
Joined
Aug 26, 2005
Messages
20
I like the dialog box approach and was able to locate the file for data transfer, however, I still see that the dates are being being brought in as text strings and not dates. When I tried to change the field type to Date/Time it deleted all the data within the column. Also, could the code be modified to delete the unused rows and columns that are in the source file spreadsheet and could the code start importing (transferring) data starting at row four?
 

nshah1013

Registered User.
Local time
Yesterday, 22:45
Joined
Aug 24, 2005
Messages
13
You can put in a range at the Docmd.Transferspreadsheet and that way you can start importing at Column D. About the date thing, I am also looking for somthing that can trasfer date as date and if you find out how to do it - it would be great if you let me know too.

DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel4, "RAW CREDIT1", OpenFile.lpstrFile, False, "D1:Z400"
 

ghudson

Registered User.
Local time
Today, 01:45
Joined
Jun 8, 2002
Messages
6,195
Creating an Import Spec should allow you to import the values in that column as dates for the field in your table.

I think you can only create an import spec for a text file.
 
Last edited:

ghudson

Registered User.
Local time
Today, 01:45
Joined
Jun 8, 2002
Messages
6,195
You can easily format the Excel file from Access. Check this thread out on how I do it.

hiding excel manipulations and showing a progress bar

That will allow you to format the values in your column to a date format before you import the file.

Also, if you set one or more of your fields in the table to "required" then the blank rows in your file will not be imported into your table.
 

NewbieUK

Registered User.
Local time
Today, 05:45
Joined
Aug 17, 2010
Messages
29
Hi There!

Hope you can help, I've used this code to import a spreadsheet which contains 9 cells worth of data. The import is working, but it's importing it 3 times so that I get 3 entries into my table. The only part of the coding I have changed is the table to import into and the excel range to import, which is a named range defined in excel.

Any ideas why it would be importing the data 3 times?

Thanks
 

NewbieUK

Registered User.
Local time
Today, 05:45
Joined
Aug 17, 2010
Messages
29
Also, is it possible to adjust the code so that the spreadsheet doesn't actually open?

Thanks
 

aquila87

New member
Local time
Yesterday, 22:45
Joined
Sep 28, 2012
Messages
5
Hi,
I have tried the code, but it's not working for me :(

It doesn't show me the error or something, but when I click on the button - nothing happen.
 

Users who are viewing this thread

Top Bottom