Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2004
    Posts
    112

    Question Unanswered: TransfareText macro to vb

    Hi all,
    I have set up a transfare text macro with spec name, table name etc. what i want to do though is when i run it i want it to ask me for a file name instead of the same file name. I know i can convert this macro to vb but does anyone know the code that will ask me for a filename.

    can anyone help

  2. #2
    Join Date
    May 2004
    Location
    The Netherlands
    Posts
    52
    Convert it, and place in the code a statement to show an inputbox. Use the entered value in the inputbox for your filename.

  3. #3
    Join Date
    Sep 2004
    Posts
    112
    Function transfare1()
    On Error GoTo transfare1_Err

    Many Thanks for your reply but since i'm new to vb where would i put the input box code? below is my converted macro showing the file name that needs to change.Also the path to the filename will allways be the same so could there be some code that enters the first part of the path as well so the user just has to enter the last part ie filename1.
    Cheers.



    DoCmd.TransferText acImportDelim, "wip1 Import
    Specification", "wip1", "c:\db\pams\filename1", True, ""
    Beep
    MsgBox "All Ok", vbOKOnly, ""


    transfare1_Exit:
    Exit Function

    transfare1_Err:
    MsgBox error$
    Resume transfare1_Exit

    End Function

  4. #4
    Join Date
    Sep 2004
    Posts
    112
    Many Thanks for your reply but since i'm new to vb where would i put the input box code? below is my converted macro showing the file name that needs to change.Also the path to the filename will allways be the same so could there be some code that enters the first part of the path as well so the user just has to enter the last part ie filename1.
    Cheers.

    Function transfare1()
    On Error GoTo transfare1_Err
    DoCmd.TransferText acImportDelim, "wip1 Import
    Specification", "wip1", "c:\db\pams\filename1", True, ""
    Beep
    MsgBox "All Ok", vbOKOnly, ""


    transfare1_Exit:
    Exit Function

    transfare1_Err:
    MsgBox error$
    Resume transfare1_Exit

    End Function

  5. #5
    Join Date
    May 2004
    Location
    The Netherlands
    Posts
    52
    Hi,
    Try the following code (just copy-past it):
    Code:
    ' Declare a variable to hold the filename
    Dim varFileName as variant
    
    ' Show inputbox to fill variable
    varFileName = InputBox("Give a filename (without path)", "Enter Custom Filename")
    
    ' Check if filename had been entered
    
    If varFileName <> "" then
      DoCmd.TransferText acImportDelim _
        , "wip1 Import Specification" _
        , "wip1" _
        , "c:\db\pams\" & varFileName _
        , True, ""
    End if

  6. #6
    Join Date
    Sep 2004
    Posts
    112
    Fantastic, Thanks that works perfectly. Wonder if you could make the routine even better??
    1, All the data in the data file 'wip1' needs deleting before i can import, could you put a routine in to delete data in the data file 'wip1' before the transfaretext routine is run?.
    2, The data file that i am importing are 'txt' files eg:'s11394.txt'. In the transfaretext routine could you add code so that the user doesnt have to type in the 'txt' extension?

    If you could help me out with the task I would be most grateful.

    Thanks.

  7. #7
    Join Date
    May 2004
    Location
    The Netherlands
    Posts
    52
    1, All the data in the data file 'wip1' needs deleting before i can import, could you put a routine in to delete data in the data file 'wip1' before the transfaretext routine is run?
    Do you meant all data in de wip1-table? If yes, write a delete query to empty the tabel, add code in your procedure to run this query (just prior to the import step).
    2, The data file that i am importing are 'txt' files eg:'s11394.txt'. In the transfaretext routine could you add code so that the user doesnt have to type in the 'txt' extension?
    Write: "c:\db\pams\" & varFileName & ".txt"

  8. #8
    Join Date
    Sep 2004
    Posts
    112
    Many thanks Robje the routine works perfect.

Posting Permissions

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