Import From Excel - Cmd Button

[Robby19]

Another question. I have a Command Button which opens the import wizard. Is there a way to, by default, have the selection to "Append a copy of the records to the table" and have the table "HSST_Master" pre-selected?

So that when a user goes in, all they have to do is Browse for the correct file on their computer and click ok?

Private Sub Command351_Click()
DoCmd.RunCommand (acCmdImportAttachText)
End Sub

 

[Micron]

Try manually creating the import spec then saving it. You should then have your named spec as a member of CurrentProject.ImportExportSpecifications. User can navigate to file name if your code uses the msoFileDialogFilePicker. You can then pass the chosen file name to the spec. Not sure if the example here presents all of the spec properties or not. If not, you could probably iterate over the properties collection for acCmdImportAttachText fairly easily.

https://www.access-programmers.co.uk/forums/showthread.php?t=260655

 

[Robby19]

Thank you for the response Micron, greatly appreciated. After reviewing the post, I don't believe I am any closer to what I was prior to. I am very inexperienced with VBA and how the codes work with each other. I would assume that the answer lies in the post by "MarkK," however I have absolutely no idea what any of that means.

 

[Micron]

Once you've manually created your import specification, try piecing code together by researching various code examples for each aspect of the task. You would find LOTS of examples on using the msoFileDialogFilePicker to find a file and pass the selected file name to a variable. MSDN and M$ in general arev valuable sources of information for all things Access and VBA. If variables are foreign, start looking there to gain an understanding of what they are, and maybe their scope. I was thinking you could adapt the code at that link, but if need be, also research the individual keywords, perhaps starting with "ImportExportSpecification". Once you've assembled some working code, post it somewhere for analysis or trouble shooting. If this is for your place of work, they could always hire someone if no one else there is capable.

 

[welshgasman]

Here is something I created the other week to do pretty much the same thing.
As Micron has stated, create your import spec by manually importing the data with all the options once and save it. Mine is called "Bank Statement Import Specification"
Then use it in the DoCmd.TransferText command

HTH

Private Sub cmdSelect_Click()
Dim fDialog As Office.FileDialog
Dim varFile As Variant
Dim strPath As String


' Set up the File Dialog. '
 Set fDialog = Application.FileDialog(msoFileDialogFilePicker)


' Set default path which is users download folder
strPath = GetDownloadsFolder()


' Disable sunform in case form left enabled from a previous run.
Me.sfrmBankStatement.Form.AllowEdits = False


With fDialog


    ' Allow user to make multiple selections in dialog box '
    .AllowMultiSelect = False
    
    ' Set the title of the dialog box. '
    .Title = "Please select Bank Statement downloaded file"
    
    ' Clear out the current filters, and add our own.'
    .Filters.Clear
    .Filters.Add "CSV files", "*.csv"
    
    .InitialFileName = strPath
    ' 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
        Me.txtFilename = .SelectedItems(1)
    Else
       MsgBox "You must select a filename!"
       Me.txtFilename = ""
       Exit Sub
    End If
End With


' Now get that data
DoCmd.SetWarnings False
SetStatusBar ("Deleting old data .....")
DoCmd.RunSQL "DELETE * from TesttblBankStatement"
SetStatusBar ("Importing selected data")
DoCmd.TransferText acImportDelim, "Bank Statement Import Specification", "TesttblBankStatement", Me.txtFilename, True
DoCmd.SetWarnings False
SetStatusBar (" ")


' Now enable subform if file selected
Me.sfrmBankStatement.Form.AllowEdits = True
Me.sfrmBankStatement.Form.Requery
End Sub

and if you want to use the downloads folder

Function GetDownloadsFolder()


'Code from https://stackoverflow.com/questions/23070299/get-the-windows-download-folders-path
'{374DE290-123F-4565-9164-39C4925E467B} is the GUID for the Downloads folder


GetDownloadsFolder = RegKeyRead("HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders\{374DE290-123F-4565-9164-39C4925E467B}")
   
'Debug.Print GetDownloadsFolder


End Function