Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jul 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro Help Needed (2000)

    I am currently trying to write a macro, that amongst other things, imports data from a csv file into a table using the transfer text option.

    Is there any way I can design it so that rather than having to specify the filepath in the design of the macro, I can bring up the window that normally appears when you go to open a file normally - if I can do this without using VBA that would be even better!!

    Any help would be appreciated!
    Cheers
    James

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro Help Needed (2000)

    Hello James,

    Welcome to Woody's Lounge!

    I don't think you can do this with macro actions only. To display the Open File dialog, you need Visual Basic. See <post#=304810>post 304810</post#>, the database attached there (Access 97, zipped) contains the code needed to do so, with a form that demonstrates how to use it. You can import the modules into your own database.

    Please feel free to ask for more assistance if you need it.

  3. #3
    New Lounger
    Join Date
    Jul 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Help Needed (2000)

    Thanks for the quick reply Hans.

    Please excuse my lack of knowledge in programming and VB, but what is the code doing with file that the user selects? And how would you recommend utilising this to export into a table (do I bypass the transfer text option in the macro?).

    Cheers
    James

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro Help Needed (2000)

    I have attached a modified version of the database I referred to (in Access 2000 format, zipped). The command button on the form lets you select a csv file to import into the database. If you open the form in design view, select the command button and then activate the Event tab of the Properties window, you will see that the On Click event contains [Event Procedure]. Click in this box, then click the button with ... to the right of the dropdown arrow (the so-called builder button). You will be taken to the Visual Basic Editor, and you will see this code:

    Private Sub cmdOK_Click()
    Dim dlg As New CommonDialog
    Const cstrTable = "tblTest"

    On Error GoTo ErrHandler

    With dlg
    .FileName = "*.csv"
    .InitDir = "C:Windows"
    .Filter = "CSV Files (*.csv)|*.csv|All Files (*.*)|*.*"
    .Flags = cdlHideReadOnly Or cdlFileMustExist Or cdlPathMustExist
    If .OpenDialog() = True Then
    DoCmd.TransferText acImportDelim, , cstrTable, .FileName, True
    MsgBox "File imported into " & cstrTable
    Else
    MsgBox "No file selected.", vbInformation
    End If
    End With

    ExitHandler:
    Set dlg = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    The essential part of the code is

    If .OpenDialog() = True Then
    DoCmd.TransferText acImportDelim, , cstrTable, .FileName, True
    MsgBox "File imported into " & cstrTable
    Else
    MsgBox "No file selected.", vbInformation
    End If

    .OpenDialog displays the dialog; if the user selects a file, this returns True, otherwise False. If True, the TransferText action is executed (DoCmd.TransferText in VBA), and the .FileName property of the dialog (the name of the file selected by the user) is supplied. The table name is a constant in this procedure, you could also prompt the user for it.
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Jul 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Help Needed (2000)

    Thanks for that Hans, that has slotted in exactly as I wanted it to.

    Cheers
    James

Posting Permissions

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