Convert data in Excel files and export (1 Viewer)

Krayna

Member
Local time
Today, 21:38
Joined
Sep 24, 2020
Messages
314
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.

Have attached system done so far. Here is more I would like it to achieve:
  • Import excel files and immediately append to table in DB so calculation can work.
  • At moment each class is queried separately so would like a user input to choose which class to query. Same idea for export.
  • Also was trying to name sheet of exported spreadsheet but kept coming up with syntax error. Not sure why?
I think that's it for now. May think of more tomorrow!

Good night and thanks :)
 

Attachments

  • 9-1Converter.accdb
    1.6 MB · Views: 33

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 21:38
Joined
Jul 9, 2003
Messages
12,809
Also was trying to name sheet of exported spreadsheet but kept coming up with syntax error. Not sure why?

I recently answered an Access World Forum users question on exporting data from a table into separate sheets in Excel. You can see my answer on the Nifty Access website here:-


The problem I had with the spreadsheet names was that if the spread sheet name I generated contain illegal characters and I got an error. I wondered if that's what might be happening in your case?

There's a video description and the code I used is at the bottom of the web page.
 

Krayna

Member
Local time
Today, 21:38
Joined
Sep 24, 2020
Messages
314
Thank you for that reference. Have actually looked at it in the past so will see if can implement here.

However main problem I'm having is IMPORTING from Excel. What I'm hoping the import function to do:
1. Select excel file (possibly preview to user)
2. User selects columns required for each column in universalGrades table (e.g. FirstName is column 2 in spreadsheet...)
3. Data is appended to table (possibly preview to user)

Thanks ppl :)
 

JMongi

Member
Local time
Today, 16:38
Joined
Jan 6, 2021
Messages
346
After rereading your thread a few times I believe your converter tool is less about automation and more about customizing an existing Access process so that non Access users can do what a knowledgeable Access user can do. I'm also not seeing an actual question other than the export spreadsheet question that was already addressed.

Tone on the internet is challenging. I am neither frustrated nor put out. Just a tad confused as to the purpose of this thread.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 21:38
Joined
Jul 9, 2003
Messages
12,809
I haven't done much with Excel.

I understand that the normal method for doing this sort of thing is to bring the basic information into Access from Excel and process it in MS Access.
 

Krayna

Member
Local time
Today, 21:38
Joined
Sep 24, 2020
Messages
314
I get you and appreciate you coming back to me. (Didn't want to nudge...😏)

Since I think I have sorted the export issue. My main question now is regarding the import process.

To explain in more detail. Function should:
1. Select excel file (possibly preview to user), the sheet structure MAY be quite erratic (with varying column names and/or number of columns). 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...).
3. Data is appended to DB table (possibly preview to user).

I think rest of conversion process will be straightforward from there.
 

JMongi

Member
Local time
Today, 16:38
Joined
Jan 6, 2021
Messages
346
I still don't see a question unless it is the inferred question, "How do I write a function to accomplish these 3 things?". So you want someone to write this function for you? That seems a bit broad does it not? I suspect you may be underestimating the complexity involved in that question if that is indeed your question.
 

JMongi

Member
Local time
Today, 16:38
Joined
Jan 6, 2021
Messages
346
That being said, the experts around here (I do not count myself in that number at all) are selfless and work hard. They've helped me a ton already. It's like asking "How do I swim the English Channel?" while sitting on the beach. But, if you dive in and start swimming, these fine people will dive in with you and help you as best they can through the process. It's awesome. It's also possible I've misread the situation. Wouldn't be the first or last time! :D
 

Krayna

Member
Local time
Today, 21:38
Joined
Sep 24, 2020
Messages
314
I definitely second you on that point @JMongi ! They literally hold your hand from beginning to end of process:)

To be honest I have dabbled in coding for import from excel but got stuck on those specific glitches mentioned above. In particular extracting required data from Excel spreadsheet and ensuring it is compatible with current table in DB.

Looking forward to some helpful responses😉
 

Krayna

Member
Local time
Today, 21:38
Joined
Sep 24, 2020
Messages
314
My system is now more or less functioning as I am anticipating.

Just quite stuck on the import and append function. Here is my code so far. Tried writing append query but not sure how to implement into function...

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

SQL:
INSERT INTO tblUniversalGrades ( fldFirstName, fldSurname, fldPercent )
SELECT fldFirstName, fldSurname, fldPercent
FROM tblTemp

And enclosed my latest DB.

Also see a typical spreadsheet to be imported.
 

Attachments

  • 9-1Converter.accdb
    1 MB · Views: 35
  • Year 8 Assessment Grades.zip
    11.5 KB · Views: 30
Last edited:

Krayna

Member
Local time
Today, 21:38
Joined
Sep 24, 2020
Messages
314
Sorry about that. Just thought since question is so heavily related to Excel, will try my luck there...

Thinking maybe I'm being over-ambitious with this whole idea?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 21:38
Joined
Jul 9, 2003
Messages
12,809
Just thought since question is so heavily related to Excel, will try my luck there...

Yes, that's a good idea. Most people here are MS Accessers.

You are welcome to ask your question elsewhere, however it is considered good practice to post links to the other places so that you don't end up with people wasting their time providing an answer that you have already received in another thread.
 

Krayna

Member
Local time
Today, 21:38
Joined
Sep 24, 2020
Messages
314
I really think there must be a way to do this in Access - I have managed all aspects (selection, preview, rename columns and append) successfully in Postgresql using Python.

Still not any closer to the solution for my import function:( Needed to hand this in last week but without being able to import the data, it is useless! It would be regrettable if all the work invested in this system would go down the drain:(
 

Slap

Registered User.
Local time
Today, 21:38
Joined
May 21, 2011
Messages
31
If the column names, not positions, are constant then a recordset based on sheet1$ where you use a sql select could work. Then read that recordset set in line by line in a loop to your table.
 

Krayna

Member
Local time
Today, 21:38
Joined
Sep 24, 2020
Messages
314
Thank you. Can you help me with implementing this?

What I'm really hoping to achieve with the IMPORT function is a replicate of the 'Import Data' wizard - would anyone have code for this?

a) I would like the user to be able to select a required sheet and columns from excel - by previewing first if possible.
b) Then transfer to Access and append to an existing table in my DB.

I guess column names can be assumed constant - or an error message is thrown to inform user to change...
 

Krayna

Member
Local time
Today, 21:38
Joined
Sep 24, 2020
Messages
314
Hi - to those who are still interested in this topic...
I have written some code for this function. Any comments or criticisms welcome!

(It can import data from spreadsheet and worksheet specified by user. It will only import data from range hard-coded and this will be appended to existing table in DB - as long as data types are compatible (column names are not relevant). )


Code:
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
 

Users who are viewing this thread

Top Bottom