Need excel macro to prompt user for date and add this to document title to open

[haganator]

Greetings everyone,

I have a macro that fills a spreadsheet with data from a number of text files. Currently the macro uses a hard-coded name to open the files, but I've made some changes so that the files now have the date added to the front of the file name. For example, one of the files is 20140427_sms2data.txt. I'm trying to "enhance" the macro to prompt the user for the date in question and then add this to _sms2data.txt to open that file. Here's my current code:

    Sheets("Daily_Status").Select
    Workbooks.OpenText Filename:= _
        "Q:\ATL\Joint\UbiCell\NOC\Daily_Alarms\20140421_sms1_daily_status.txt", Origin _
        :=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
        , ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, Comma:=False _
        , Space:=True, 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), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), _
        Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array( _
        23, 1), Array(24, 1), Array(25, 1)), TrailingMinusNumbers:=True

Any help would be greatly appreciated.

Thanks,
Jeff

 

[CharlesH]

Hi

Something like this may work.
You will however need to code if user click "Cancel"
Not fully tested.

Sub test()
Dim strMyDate As String
    strMyDate = InputBox("Please enter Dtae")
    if strMyDate = "" Then Exit Sub''''Added
Sheets("Daily_Status").Select
    Workbooks.OpenText Filename:= _
        "Q:\ATL\Joint\UbiCell\NOC\Daily_Alarms\" & strMyName & "_" & "sms1_daily_status.txt", Origin _
        :=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
        , ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, Comma:=False _
        , Space:=True, 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), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), _
        Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array( _
        23, 1), Array(24, 1), Array(25, 1)), TrailingMinusNumbers:=True
End Sub

 

[haganator]

Hi

Something like this may work.
You will however need to code if user click "Cancel"
Not fully tested.

Sub test()
Dim strMyDate As String
    strMyDate = InputBox("Please enter Dtae")
    if strMyDate = "" Then Exit Sub''''Added
Sheets("Daily_Status").Select
    Workbooks.OpenText Filename:= _
        "Q:\ATL\Joint\UbiCell\NOC\Daily_Alarms\" & strMyName & "_" & "sms1_daily_status.txt", Origin _
        :=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
        , ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, Comma:=False _
        , Space:=True, 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), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), _
        Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array( _
        23, 1), Array(24, 1), Array(25, 1)), TrailingMinusNumbers:=True
End Sub

I knew it wasn't as involved as I was making it. That works like a charm. I need to play around with a couple different iterations, but so far so good. Thanks for your help.

Jeff