Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2007

    Unanswered: Macro code to open a different file each time.

    Hi guys, I need to open a different file each day - how do I put a halt to the macro I have so I can select the file I want to use? The macro I use is below - the file name that needs to alter each day is shown in red.

    With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;K:\My files\Billing\BILLER INFORMATION FILE20130516" _
    , Destination:=Range("A1"))
    .Name = "BILLER INFORMATION FILE20130516"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 850
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 2)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
    End With
    Selection.Insert Shift:=xlToRight
    ActiveCell.FormulaR1C1 = "=RC[-6]*100"
    Selection.AutoFill Destination:=Range("I1:I68")
    End Sub

  2. #2
    Join Date
    Apr 2004
    Derbyshire, UK
    Provided Answers: 2

    You have not explaned what exactly you want to replace it with (and I hvwe never used QueryTables), but, just guessing would this be something like:-

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;K:\My files\Billing\BILLER INFORMATION FILE" & Format(Date, "yyyymmdd"), Destination:=Range("A1"))
        .Name = "BILLER INFORMATION FILE" & Format(Date, "yyyymmdd")
    End With


  3. #3
    Join Date
    Aug 2007
    Sorry, here is more explanation. Each day I download a file from the bank - I need to pull that file into an Excel spreadsheet that is set up to convert the data supplied by the bank, into the format needed to then be uploaded to our company financial system. Each file I get from the bank has a different file name (in order to differentiate it from previous files). So what I am after is the VBA coding that will start the process of opening a file and then allow me to choose the file I want opened. Does that make more sense?

  4. #4
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 18
    If you know the name of the source file, use the InputBox function to prompt for it and capture it in a variable. Then use the variable in place of the hard coded value.

    Or use the Application.FileDialog property to use the standard File Open dialogue window to navigate to the relevant file.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Posting Permissions

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