Better way to DoCmd.TransferSpreadsheet

[sambucaman]

Hi everyone.

I'm looking for a better way to auto import data to a table, via a click button on a form. I am currently using the below method...

Private Sub import_Click()
MsgBox "You are about to import the photographers work into this database. Please ensure that the staff database is renamed 'customers.xls' and saved on your desktop.."

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Customers", "c:\users\photographer\desktop\Customers.xls", True
DoCmd.Close
DoCmd.OpenForm "Customers"
End Sub

The above has a few drawbacks, mainly that each user / pc has a different logon, so a different path. Each time I update or change the DB, I then have to go round the office and change the code on each PC. The other bad flaw is we cant see which file we are selecting, and I'm relying on people to manually name their xls file correctly.

Does anyone know of some VBA that will go in here to open a browse box, allowing user to browse to ANY .xls file and selecting that?

Have experimented with browsing for files before, never had any success.
Thanks in advance.

 

[GregRun]

EDIT: This requires a reference to the Microsoft Office 12.0 Object Library.

Private Sub import_Click()
    Dim fDialog As Office.FileDialog
    Dim varFile As Variant
    Dim CustomerFile as String
    
    'Set up the File Dialog.
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    With fDialog
       'Allow only one file to be selected
       .AllowMultiSelect = False
             
       'Set the title of the dialog box.
       .Title = "Please select your customer file."
    
       'Clear out the current filters, and add our own.
       .Filters.Clear
       .Filters.Add "Excel Spreadsheets", "*.xlsx"
       'Set the default location to the current user's desktop
       .InitialFileName = "C:\Users\" & CurrentUser & "\Desktop"
       
       'Show the dialog box. If the .Show method returns True, the
       'user picked at least one file. If the .Show method returns
       'False, the user clicked Cancel.
       If .Show = True Then
          'Loop through each file selected and add it to the list box.
          For Each varFile In .SelectedItems
             CustomerFile = varFile
          Next
       End If
    End With

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    "Customers", CustomerFile, True

    DoCmd.OpenForm "Customers"
    End Sub

 

[vicsar]

I might be using this but I have made a (small) improvement. Check out the error handler.

Option Compare Database
Option Explicit

'---------------------------------------------------------------------------------------
' Module       : XXModuleNameXX
' Author       : [URL="http://www.access-programmers.co.uk/forums/member.php?u=101922"]GregRun[/URL] , Error handler by [URL="http://www.access-programmers.co.uk/forums/member.php?u=108288"]vicsar[/URL]
' Date         : 2/8/2013
' Purpose      : Used to import data into customers table
' Requirements : Requires a reference to the Microsoft Office 12.0 Object Library.
'---------------------------------------------------------------------------------------


Sub Import_Posting_Dates()
Dim fDialog As Office.FileDialog
Dim varFile As Variant
Dim CustomerFile As String
    
' Set up the File Dialog.
On Error GoTo Import_Posting_Dates_Error
    
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
        With fDialog
' Allow only one file to be selected
            .AllowMultiSelect = False

' Set the title of the dialog box.
            .title = "Please select your customer file."
    
' Clear out the current filters, and add our own.
            .Filters.Clear
            .Filters.Add "Excel Spreadsheets", "*.xls"
            .Filters.Add "Excel Spreadsheets", "*.xlsx"
                        
' Set the default location to the current user's desktop
            .InitialFileName = "H:\My Documents"
  
' Show the dialog box. If the .Show method returns True, the
' user picked at least one file. If the .Show method returns
' False, the user clicked Cancel.
       If .Show = True Then
' Loop through each file selected and add it to the list box.
          For Each varFile In .SelectedItems
             CustomerFile = varFile
          Next
       End If
    End With

' See error handler, what to do if operation is cancelled (2522)
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    "Customers", CustomerFile, True

    DoCmd.OpenForm "Customers"

   On Error GoTo 0
   Exit Sub

Import_Posting_Dates_Error:
    If Err.Number = 2522 Then ' 2522 missing argument (File Name) - i.e no file selected
        MsgBox "No file selected, operation cancelled " & "( " & Err.Number & " ).", vbInformation, "APP NAME
    Else
        MsgBox Err.Number & " (" & Err.Description & ")", vbInformation, "APP NAME"
    End If
    
End Sub

 

[Alansidman]

Here is somthing I found a year or so ago that may be helpful

 

[gemma-the-husky]

not only do i use a file browser to select the file, i also tend to add code to validate the columns in the spreadsheet - to ensure use of the correct file.

 

[vicsar]

Nice. Now, here is something to puzzles me. How do I go about adding additional data to existing records?

For instance if I have the following table:

ID | DataField1 | DataField2 | DataField3
1 | Somte Data | More data| xcetera
2 | Somte Data | ________ | xcetera
3 | Somte Data | More data| xcetera

and want I it to become this, by importing data from an Excel file

ID | DataField1 | DataField2 | DataField3
1 | Somte Data | More data | xcetera
2 | Somte Data | NEW DATA, ADDED FROM EXCEL IMPORT | xcetera
3 | Somte Data | More data | xcetera

What are the steps I should take to achieve it?

The idea I have is to provide a template to the users, they would input the missing data using as reference the ID field (unique identifier), then save the template it in their computers. Later they could just click on a button inside an Access form, the button would allow them to browse for the file and import and append the data into an existing table and records.

Any suggestions?

 

[GregRun]

It appears you've taken full credit for my code...

 

[DavidAtWork]

Viscar to achieve your second point, you need to do it in 2/3 steps, first import the data to a temp table which should be a design copy of the original table, import new data file to temp then use it to update original if a record already exists otherwise append new record
Process would be for each file you want to import:
empty the temp table
import new data to temp
append any new records not in original
use temp to update original records that exist

This assumes that some records could be updated from any one of many sources

David

 

[vicsar]

It appears you've taken full credit for my code...

Hey GregRun, yes it would seem so, but I just did not remove or update the default MZ-Tools header -I was in a hurry. I have corrected the code. Thanks for bringing it up.

 

[vicsar]

Viscar to achieve your second point, you need to do it in 2/3 steps, first import the data to a temp table which should be a design copy of the original table, import new data file to temp then use it to update original if a record already exists otherwise append new record
Process would be for each file you want to import:
empty the temp table
import new data to temp
append any new records not in original
use temp to update original records that exist

This assumes that some records could be updated from any one of many sources

David

The agony... that is how I originally envisioned it, I just did not want to go to all that hassle. I tough there was a better, faster, painless, easy way

Thanks for replying.

 

[GregRun]

Hey GregRun, yes it would seem so, but I just did not remove or update the default MZ-Tools header -I was in a hurry. I have corrected the code. Thanks for bringing it up.

No problem at all. Happy to help.

As far as your update to the error handler it shouldn't be necessary. If the user doesn't select a file it wool just close. If you want to show an error if they don't pick anything, put an else in the "If .show = true" statement. You should try to code to avoid errors in the first place rather than handle that specific error when it comes up.

 

[AndrewS]

Hi everyone.


The above has a few drawbacks, mainly that each user / pc has a different logon, so a different path. Each time I update or change the DB, I then have to go round the office and change the code on each PC. The other bad flaw is we cant see which file we are selecting, and I'm relying on people to manually name their xls file correctly.

Resurrecting an old thread, and you've no doubt got this working now, but one thing that I don't think was addressed in responses above is the filepath.

You can still use the old DOS variables in paths (even in Windows 10), so, for example,

.InitialFileName = "%USERPROFILE%\Desktop\"

will set the default file location to the current user's desktop.