Import Excel sheet to Access

Krayna

New Member
Joined
Feb 2, 2021
Messages
34
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,223,162
Messages
6,170,432
Members
452,326
Latest member
johnshaji

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
Back
Top