Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Macro to Open Worksheet (Excel 2002)

    Hi!
    I have downloads from an external source that I open in Excel using the text import wizard, delimited, based on tabs & commas. The macro below is what I've recorded. The actual name of the downloaded file will change every month - in this case the file is named GDXXB03P.GLI - the "03" in the file name will change next month to "04". I'd like the macro to read the variable portion of the file name from the worksheet I currently have open... in the code below the original code, I gave it a whirl and tried to concatenate the "03P" into the file name in the macro and, of course, it didn't work... as always, any help would be much appreciated!
    Thanks!!
    Lana

    Workbooks.OpenText Filename:= _
    "K:groupsFinance2007Bank ReconciliationsPayroll AccountGDXXB03P.GLI", _
    Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
    Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
    Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
    Array(9, 1), Array(10, 1)), TrailingMinusNumbers:=True


    My attempt
    Workbooks.OpenText Filename:= _
    "K:groupsFinance2007Bank ReconciliationsPayroll AccountGDXXB & Sheets("ADP").Range("A1").GLI", _
    Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
    Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
    Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
    Array(9, 1), Array(10, 1)), TrailingMinusNumbers:=True

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

    Re: Macro to Open Worksheet (Excel 2002)

    Use

    "K:groupsFinance2007Bank ReconciliationsPayroll AccountGDXXB" & Sheets("ADP").Range("A1") & ".GLI"

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro to Open Worksheet (Excel 2002)

    Ahhh... I was actually closer than I thought I was!! This works perferct!
    Thanks Hans!
    Lana

Posting Permissions

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