Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old September 26th, 2007, 02:54 PM
Authorized User
 
Join Date: Nov 2006
Location: , , .
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default importing spreadsheet with field mappings

Hi all,

I need to import a spreadsheet into a specific table in Access. My problem is that not all the field names match (but i do know the possible headers for a specific field).... so there need to be some mapping. The "DoCmd.TransferSpreadsheet" does not work because of the mapping problem. Is there any way I can do this ?

I need to -
1. ask the user for the filename as input
2. let user do the mapping
3. import the file

Thank you all for ur help and suggestions!
Reply With Quote
  #2 (permalink)  
Old September 27th, 2007, 10:15 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

There are some different ways to do this. The way I would suggest involves coding the import to open a file dialog box, have the user select the file, then read it with a Scripting.FileSystemObject, take the field names, allow the user to select matching fields for the spreadsheet fields using a form, then importing line by line with code. Sort of brute force coding. Is that the way you want to go? Do other posters have a more elegant solution?





mmcdonal
Reply With Quote
  #3 (permalink)  
Old September 27th, 2007, 10:44 AM
Authorized User
 
Join Date: Nov 2006
Location: , , .
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi mmcdonal,

Could u plz post the code for this.... this looks hard to me.
Thanks

Reply With Quote
  #4 (permalink)  
Old September 27th, 2007, 11:40 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

See, here is where the Wrox Reference Library online really pays off. I went there and did a search for these actions and put this code together to open the File Dialog box to the folder of your choice, to display only .xls files. I used the C:\ root, but you could choose the default folder. I also set the MultiSelect to False since we only want to deal with one file at a time:

Dim dlgOpen As FileDialog
Dim vPath As Variant
Dim sPath As String

Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)

With dlgOpen
    .AllowMultiSelect = False
    .InitialFileName = "C:\*.xls"
    If .Show = -1 Then
        For Each vPath In .SelectedItems
        sPath = vPath
        Next
    End If

End With

This takes the path to the Excel file into the variable sPath if the user selects a file.

More after I do more research in the VBScript reference online.

What I intend after the user selects an Excel file is to do the following:

Save the file as a .csv file.
Read the .csv file, and take the first row as column headings. Is this accurate?
Then show those column headings in a list and allow the user to match them to column headings in your existing table.
Can you post the column headings from the existing table please?


mmcdonal
Reply With Quote
  #5 (permalink)  
Old September 27th, 2007, 03:01 PM
Authorized User
 
Join Date: Nov 2006
Location: , , .
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

that's exactly what i'm trying to do.

the column headings from existing table are -
Part_id
Cust_name
Contra_Num
Share_Num
Rpt_Num
Trnx_num

Thanku

Reply With Quote
  #6 (permalink)  
Old September 28th, 2007, 08:47 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Okay, this solution is going to rely on you creating a .csv file out of the .xls file. I was having trouble duplicating the .csv file with code since it turned the .xls file to crap.

I created a module and put this in it to create a Public variables to carry the Path between two forms and to carry the headings between the combo boxes:

Public pPath as String
Public HeadArray As Variant

I built Form1 with this code on a single button to pick the .xls file (the user doesn't need to know they are working from a .csv file.

Private Sub Command0_Click()
Dim dlgOpen As FileDialog
Dim vPath As Variant
Dim sDoc As String
'pPath is a Public String variable declared in a Module

Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)

With dlgOpen
    .AllowMultiSelect = False
    .InitialFileName = "C:\*.xls"
    If .Show = -1 Then
        For Each vPath In .SelectedItems
        pPath = vPath
        Next
    End If

End With

sDoc = "Form2"

DoCmd.Close

DoCmd.OpenForm sDoc, acNormal

End Sub

That closed Form1 and opens Form2. This code is on the On Open event of Form2. Form2 has 6 combo boxes on it, and each label is one of the field names from you local table. Each of them has the Row Source type set to Value List. This code populates the first combo with the headings from the .csv column names and disables the other combo boxes.

Private Sub Form_Open(Cancel As Integer)
Dim objFSO As Variant
Dim objFile As Variant
Dim PathArray As Variant
Dim FileArray As Variant
Dim sFileName As String
Dim sNewFile As String
Dim iFile As Integer
Dim iPath As Integer
Dim sPath As String
Dim i As Integer
Dim x As Integer
Dim myControl As Control

'Warn if pPath is empty
If pPath = "" Then
    MsgBox "Please choose a file", vbCritical
    DoCmd.OpenForm "Form1", acNormal
    Cancel = True
End If

'This give you your File Name
PathArray = Split(pPath, "\")
iFile = UBound(PathArray)
sFileName = PathArray(iFile)
FileArray = Split(sFileName, ".")
sFileName = FileArray(0)

'This gives you the Path to the file
sPath = PathArray(0)
i = 1
Do Until i = UBound(PathArray)
    sPath = sPath & "\" & PathArray(i)
i = i + 1
Loop
'Puts closing backslash if needed
If iFile <> 0 Then
    sPath = sPath & "\"
End If

'This gets the csv version of the file
sNewFile = sPath & sFileName & ".csv"
Set objFSO = CreateObject("Scripting.FileSystemObject")

'Now get the first row
Set objFile = objFSO.OpenTextFile(sNewFile, 1)
i = 0
Do Until i = 1 'objFile.AtEndOfStream
    If i = 0 Then
        sHeading = objFile.ReadLine
    End If

    If i = 0 Then
        HeadArray = Split(sHeading, ",")
        Set myControl = Me.Combo0
        With myControl
            Do Until x = UBound(HeadArray) + 1
                .AddItem HeadArray(x), x
            x = x + 1
            Loop
        End With
    End If
i = i + 1
Loop

objFile.Close
Me.Combo2.Enabled = False
Me.Combo4.Enabled = False
Me.Combo6.Enabled = False
Me.Combo8.Enabled = False
Me.Combo10.Enabled = False

End Sub

The next thing I need to do is to create On Click events for each of the combo boxes and put the selections into their own public variables. Then open enable the next combo box, but limit its list to the HeadArray minus the current selections. There should also be a reset button in case they mess up with the selections. Or there should be a button to reset a particular combo box.

Then there should be a load button that uses the values from the combo boxes to move the data in from the .csv file. Can you figure any of these steps out? The online reference library is a big help with this.


mmcdonal
Reply With Quote
  #7 (permalink)  
Old September 28th, 2007, 08:50 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

P.S. I could have used the objFSO.GetParentFolderName(objFile) instead of creating it from the array. I think. There was a reason I didn't do this, but I can't remember. You may want to try that.


mmcdonal
Reply With Quote
  #8 (permalink)  
Old September 28th, 2007, 08:51 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Heck, alternatively, you could just have the user choose a .csv file, but we may get the .xls code working, and this doesn't take many more clock cycles the way it is.

mmcdonal
Reply With Quote
  #9 (permalink)  
Old September 28th, 2007, 08:52 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Also, I should have set x = 0 somewhere before I use it, but 0 is the default value of an integer variable, so not really an issue. It is just better coding to do add that bit.

mmcdonal
Reply With Quote
  #10 (permalink)  
Old January 11th, 2017, 12:48 PM
Registered User
Points: 3, Level: 1
Points: 3, Level: 1 Points: 3, Level: 1 Points: 3, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2017
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Do you have an update on this code?

This is exactly what I'm needing to do for our database here at NASA CVL.. it would be helpful if you could please provide your final solution that you implemented.
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Importing spreadsheet data with more control tencelos Access VBA 2 September 1st, 2006 11:30 AM
Importing a bitmap to an ole field rohan_man Access 6 February 13th, 2005 11:57 PM
Problem importing data into a TEXT field seansheds SQL Server DTS 1 August 19th, 2004 03:52 PM
Adding the records into Field F1 while importing a jcui@bankofny.com VB Databases Basics 0 August 6th, 2004 02:43 PM
Adding the records into Field F1 while importing a jcui@bankofny.com Access 0 August 6th, 2004 02:06 PM



All times are GMT -4. The time now is 06:32 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.