Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    10

    Unanswered: Is it possible with VBA?

    I would like to make a form in Access2000, with a button when on-click,
    open a browser to let the user choose the Excel file, then import the spreadsheet into Access 2000 as a new table.
    How to do that? Thanks.

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    This is what I use to get files (I think you need a reference to the Microsoft Office Lib, either that or Scripting Runtime):

    Private Sub cmdSelectFiles_Click()
    'Declare a variable as a FileDialog object.
    Dim fd As FileDialog
    Dim strFiles As String, strInitialView As String

    strInitialView = "C:\"

    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    Dim vrtSelectedItem As Variant

    With fd
    .Filters.Add "Excel Files", "*.xls; *.xl*", 1
    .InitialFileName = strInitialView
    If .Show = -1 Then

    For Each vrtSelectedItem In .SelectedItems

    strFiles = strFiles & vrtSelectedItem & ";"

    Next vrtSelectedItem
    Else
    Set fd = Nothing
    Exit Sub
    End If
    End With
    strFiles = Left(strFiles, Len(strFiles) - 1)
    Me.lboxSelFiles.RowSourceType = "Value List"
    Me.lboxSelFiles.RowSource = strFiles

    Set fd = Nothing
    End Sub

    And this is what I use to import:

    Private Sub cmdImport_Click()
    On Error Resume Next

    ' CREATE AN OBJECT VARIABLE TO HOLD THE REMOTE DATABASE
    Dim appAccess As Access.Application
    Set appAccess = CreateObject("Access.Application")

    appAccess.OpenCurrentDatabase “YourPathAndDatabaseName”
    appAccess.Visible = False
    Me.Repaint
    ' CLEAR ANY EXISTING RESULTS
    Me.txtError.Value = ""
    Me.txtSuccess.Value = ""
    ' NEED TO DETERMINE WHAT WAS CHOSEN: ALL FILES OR SELECTED FILES

    Dim i As Integer, j As Integer, SnipLeft As Integer, strFN As String
    For i = 0 To Me.lboxSelFiles.ListCount - 1 ' ITERATE THROUGH THE LIST BOX
    strFN = Me.lboxSelFiles.ItemData(i)
    For j = Len(strFN) To 1 Step -1 ' ITERATE THROUGH THE FILE NAME
    If Mid(strFN, j, 1) = "\" Then
    SnipLeft = j
    Exit For
    End If
    Next j
    strFN = Mid(strFN, SnipLeft + 1, Len(strFN) - SnipLeft - 4) ' -4 to account for .xls

    ' DELETE THE EXISTING TABLE IN THE REMOTE DATABASE, IF IT DOES NOT EXIST, CLEAR THE ERROR
    appAccess.DoCmd.DeleteObject acTable, strFN
    If Err.Number <> 0 Then Err.Clear

    appAccess.DoCmd.TransferSpreadsheet acImport, , strFN, Me.lboxSelFiles.ItemData(i), True

    If Err.Number <> 0 Then
    txtError.Text = txtError.Text & strFN & "-" & Err.Number & "-" & Err.Description & vbCrLf
    Err.Clear
    Else
    txtSuccess.Text = txtSuccess.Text & strFN & vbCrLf
    End If
    Next i
    End Select

    appAccess.Quit acQuitSaveAll
    Set appAccess = Nothing

    End Sub

    HTH.
    All code ADO/ADOX unless otherwise specified.
    Mike.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •