Import Excel sheet to Access

[Krayna]

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.

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

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

I think that's it for now. Thank you

 

[matthenry]

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]

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]

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]

Thank you for clarifying the query.

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

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:

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]

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]

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.

 

[welshgasman]

Crossposted https://www.access-programmers.co.uk/forums/threads/convert-data-in-excel-files-and-export.316088/#post-1746679

 

[Joe4]

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]

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...