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

 

[Krayna]

So are you saying that the only way to do this is first edit in Excel? Is there no way to automate?

 

[xenou]

To clarify - I didn't really say the only way to do this was to first edit in Excel. It more like I was saying that *you said* you wanted to first edit in Excel... sorry if that's confusing.

From your first post:

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.

and:

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

So my comment/question is what is your expectation for the user to be doing these things in Excel, given you also expect them to be primarily working in Access (and would like this to be automatic-ish?)?

 

[Micron]

I don't understand why Access needs to be involved. What calculations can you do in Access that you can't do in Excel? I would have thought that calculation limitations would be the other way around.
What's a "conversion calculation" anyway?

 

[Krayna]

My user will mainly be focusing on data aggregation which is best achieved in Access. There probably can be a similar solution devised in Excel, however as I have already invested many hours to create the system in Access, I am wary to restart in Excel (especially as I my experience with Excel is limited).
As you may have seen the data to be imported is students grades which must be converted using particular grade boundaries. Eventually this tool may be incorporated into a commercial school management system being developed - another reason why Access has been implemented.

Was hoping to have this component finished last week but have been seriously stuck on this import function - without it rest of my system is USELESS as the user must be able to combine data from excel spreadsheets. I guess if the data is manipulated in Excel to correspond with the desired table in Access, this can be allay some of the automation from the Access side. I can imagine it is possible to call an Excel function from Access to do the first two steps required?

It will be unfortunate if I do not find any means to resolve this in the next day or two...I was very much looking forward to present the system to my boss but at moment it is not happening

 

[Micron]

I've reviewed your comments here and at AWF (where I used to help but don't any longer) and here are my problems
a) you don't address comments/questions/assumptions when they are presented
b) the problem/goal is still not clear. You show code for transferpreadsheet and say that it works, so I don't know what you're asking for now.
c) you say you want to stick with Access, yet you want to put the modified data back in to Excel at the end of this process, which still makes me wonder why not just do calculations there. Never mind, I'm just going to go with your convictions.
d) beyond stating 'I want to do this and this' there doesn't seem to be a clear definition of a problem. At least not to me.

I don't have time to monitor another forum for the same problem to see what information I can glean from it, or what has been suggested there. So in absence of a clear and concise issue I will have to unwatch this thread I guess.

 

[Krayna]

Firstly congrats on reaching 2000 posts!

I apologise for my lack of clarity. So maybe I've been expecting too much from here...sometimes I've been lucky enough to obtain full functions written out in code from AWF. Here I have started writing some code but my knowledge of automation is still quite limited so hoping for some contributions...
I will try to reiterate my difficulty once more:

I currently have the transferSpreadsheet method which is able to take an excel spreadsheet and copy to a new table in Access.

a) I would like the user to be able to select a required sheet and columns from excel - by previewing first if possible. (This is very similar to how Access' import wizard works - but would like user to do without Access tools if possible.)
b) Then transfer to Access and append to an existing table in my DB.

Is this so difficult??

 

[Micron]

I'd say it is, and not very attractive as a non paying job - especially when it's just reinventing the wheel. I know what you mean about free code at AWF but I doubt that even those who will remain nameless here will take up the challenge for free. That's just my take.

At the 7th post at AWF they still don't understand what you want. My suggestion would be to tell them you want to replicate the Get Data wizard that allows you to select a file, select a sheet, choose to import, keep or drop columns and then make a table from the resulting selection. Maybe you want to define data types too so that the table is constructed properly. Don't forget that in the end, it should somehow be easier for your user than the built in wizard is. That should clear up their confusion, and if anyone is willing to build that for you, you'll be off to the races.

BTW thanks for the congrats. I didn't even notice the post count.

 

[xenou]

It would be best to simply require the names in the spreadsheet be the same as the names in the database. I would drop all the fldThis and fldThat naming convention. A table field is a table field, you don't need prefixes to figure it out. Just use sensible names that your tables and your Excel columns can share. So then you can just map column names to field names and that's that (setting bad data and inconsistent data types aside, but you'd have that problem anyway whether or not names match up).

Having the user have to go through a mapping process to select and match up field names from sheets to tables, every time they do an import, is a tedium you don't want for your users. But also having an 'application' that is half in Excel and half in Access is probably bad design, especially if your users have to navigate those differences in an explicit way that requires work on their part. Its one thing to have a DB backend. It's quite another to get your users involved in mapping data to the back end from the front end. So I would also recommend that the data processing be done in Excel, or that Excel be removed from the equation and everything be done in Access.

Having said that, the simplest "import wizard" I can imagine is that you create a form in Access that reads the Excel workbook and displays the sheets and columns found (in some fashion) and allows the user to select columns to import. But as I said, if you have to worry about changing and inconsistent column names and inconsistent data types or invalid data then it's very difficult to do this at all (and you would have to consider carefully whether the already built in import wizard doesn't already do the job you need).

 

[Krayna]

Thanks for your comments. Definitely a valid point to move the entire system to Excel - which would probably be possible considering the processes required. However this is a very small project, I have not been commissioned many hours to complete it and will therefore not spend time at moment restructuring it - although if the client requires drastic changes, I may do so.
I have actually manage to write semi-adequate code to do task I was seeking. It actually eliminates the requirement to have identical titles as DB table, however it will only work with the columns specified in code. Although it does allow user to select sheet required. And it will append to my current table.
Here is my code. Any comments or criticisms are welcome!

Private Sub cmdImportExcel_Click()


Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim blnEXCEL As Boolean
Dim fd As FileDialog
Dim item As Variant
Dim strPathFile As String
Dim blnHasFieldNames As Boolean
Dim sheetName As String

blnEXCEL = False

' Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
      Set xlx = CreateObject("Excel.Application")
      blnEXCEL = True
End If
Err.Clear
On Error GoTo 0

' Change True to False if you do not want the workbook to be
' visible when the code is running
xlx.Visible = True

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True


Set fd = Application.FileDialog(msoFileDialogOpen)
With fd
.AllowMultiSelect = False
.Title = "Please select an Excel Spreadsheet"
.Filters.Clear
.Filters.Add "Both File Types", "*.xls, *.xlsx, *.csv, *.txt"

If .Show Then
strPathFile = .SelectedItems(1)
End If
End With

sheetName = InputBox("Enter sheet name to import", "", "Sheet1")

' Replace C:\Filename.xls with the actual path and filename
' of the EXCEL file from which you will read the data
Set xlw = xlx.Workbooks.Open(strPathFile, , True) ' opens in read-only mode

' Replace WorksheetName with the actual name of the worksheet
' in the EXCEL file
Set xls = xlw.Worksheets(sheetName)

' Replace A1 with the cell reference from which the first data value
' (non-header information) is to be read
Set xlc = xls.Range("A2") ' this is the first cell that contains data

'clean the existing table
ClearTable

Set dbs = CurrentDb()

' Replace QueryOrTableName with the real name of the table or query
' that is to receive the data from the worksheet
Set rst = dbs.OpenRecordset("tblUniversalGrades", dbOpenDynaset, dbAppendOnly)

' write data to the recordset
Do While xlc.Value <> ""
      rst.AddNew
            For lngColumn = 0 To rst.Fields.Count - 1
                  rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
            Next lngColumn
      rst.Update
      Set xlc = xlc.Offset(1, 0)
Loop

rst.Close
Set rst = Nothing

dbs.Close
Set dbs = Nothing

' Close the EXCEL file without saving the file, and clean up the EXCEL objects
Set xlc = Nothing
Set xls = Nothing
xlw.Close False
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing



 
End Sub

 

[Micron]

How does that allow the user to choose sheet column A, B, D,F but not C or E? That is more or less what you asked for.