Import Excel sheet to Access

Krayna

New Member
Joined
Feb 2, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
Hi,

I am attempting to create a convertor tool in Access which will take values in a given spreadsheet, perform the conversion calculation and then export back to excel.
So far I have managed to perform the calculation effectively as well as export in a limited way. Basically I am just looking to brush up my tool so it can be used independently by other users.

I would like to be able to import excel files of variable structures and immediately append to table in DB so calculation can work.
To explain in more detail. Function should:
1. Select excel file (possibly preview to user), - see below how a sheet MAY look however it is quite erratic. User should be able to select which sheet in file required.

excel sheet to import.png

2. User selects columns required for each column in DB table (e.g. FirstName = column 2 in spreadsheet...) - see table structure below.
table in Access.png

3. Data is appended to table (possibly preview to user).

I think that's it for now. Thank you
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

matthenry

Spammer
Joined
Feb 4, 2021
Messages
4
Office Version
  1. 2010
Platform
  1. Windows
To import an Excel spreadsheet into a new table in Access:
Open the Access database.
If you receive a security warning, click the Enable Content button.
On the Office ribbon, select the External Data tab and click Excel.
The "Get External Data - Excel Spreadsheet" wizard appears. In the File name field, browse to the Excel file. Select the "Import the source data into a new table in the current database" option and click OK.
Select the worksheet to import. Click Next.
If the first row contains headers, mark the "First Row Contains Column Headings" checkbox. Click Next.
Select the options for each column or just leave it at the default and click Next.
Accept the default of "Let Access add primary key." Click Next.
The Import to Table field defaults to the worksheet name. Update it if needed. Click Finish. The worksheet imports into a table.

I hope these steps will be helpful!
Matt Henry
 

Krayna

New Member
Joined
Feb 2, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
Hi - thanks for your response. I have used your method successfully. However I am attempting to build a system which is as automated as possible for the user hence I would like a FUNCTION which can achieve this process with minimum user input. Any more ideas?
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,059
Office Version
  1. 365
Platform
  1. Windows
It would require Automation and a LOT of code. That is because you want to automate/provide for
- file selection
- sheet selection in a file
- sheet preview
- column selections in a selected sheet (assuming not rows as well)
- transfer of only selected ranges, which transferspreadsheet function can do IF importing into Access. AFAIK, it will also create a new table if you don't specify a name.
Re: #3 - that implies the table already exists when you started but I don't see how that's possible if someone can arbitraryily select files/sheets/columns. If using transferspreadsheet you shouldn't have to append to a table as was noted. Perhaps CopyFromRecordset is another option to get data into a table, but if you have to make that table at the same time, more code.
That's my 2 cents.
 

Krayna

New Member
Joined
Feb 2, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thank you for clarifying the query.

So far I have managed the following VBA code to import:

VBA Code:
Public Function ImportXL() As Boolean

Dim fd As Object
Dim strFile As String

Set fd = Application.FileDialog(3)

With fd
    .AllowMultiSelect = False
    .Filters.Clear
    .Filters.Add "Excel Files", "*.xls*"
    If .Show Then
        strFile = .SelectedItems(1)
    End If
End With

If strFile = "" Then
    ImportXL = False
Else
    On Error GoTo BadFormat
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tblTemp", strFile, True
    ImportXL = True
End If

Set fd = Nothing
Exit Function

BadFormat:
MsgBox "The file you tried to import was not an Excel spreadsheet."


End Function

And this SQL query may work to append:
SQL:
INSERT INTO tblUniversalGrades ( fldFirstName, fldSurname, fldPercent )
SELECT fldFirstName, fldSurname, fldPercent
FROM tblTemp

I see that it's not possible to attach my DB to this thread. Here is a link.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,825
Office Version
  1. 2019
Platform
  1. Windows
Are you intending your user to be working in Access and import from Excel (pull data), or to be working in Excel and send the data to Access for processing (push data). The amount of interaction with excel (selecting sheets, selecting columns) seems to suggest the latter, but the fact that you are writing transfer spreadsheet functions in Access seems to suggest the former. So I'm not sure what your expected workflow is intended to be.
 

Krayna

New Member
Joined
Feb 2, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The main functionality of my system takes place in Access. User will require data from Excel to manipulate using Access queries and then this can be output as a further Excel file.
Hope that makes sense.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,703
Office Version
  1. 365
Platform
  1. Windows
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.
Be sure to follow & read the link at the end of the rule too!

If you have posted the question at more places, please provide links to those as well.
If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,825
Office Version
  1. 2019
Platform
  1. Windows
The main functionality of my system takes place in Access. User will require data from Excel to manipulate using Access queries and then this can be output as a further Excel file.
Hope that makes sense.
I wouldn't say it entirely makes sense. If you are working in Access, you can import data from Excel (somewhat easily, more or less). You cannot select sheets and columns in Excel (easily) - for the obvious reason that you aren't working in Excel which is where you would usually be when you are selecting sheets and columns...
 

Watch MrExcel Video

Forum statistics

Threads
1,127,830
Messages
5,627,145
Members
416,223
Latest member
RichardHell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top