Prompt user for date and user to Open file

[Pamella]

I would like the user to enter a date and this should be entered in the next unused cell in column A.
Then prompt the user to to open a text file. This text file should be added as a worksheet and named the same as the date just entered.

Sub Workbook_Open()
   Dim dte As String
   Dim usedrow As Long
   Dim fNameAndPath As Variant
   Dim strMaster As String
   
   strMaster = Application.ActiveWorkbook.Name
   usedrow = .Cells(Rows.Count, "A").End(xlUp).Row
   
'Ask user to Enter date
   dte = InputBox("Please Enter Date: ", Default:=Format(Now, "dd-mmm-yy"))
   
   'validate input
   
   'output
   Sheets("Main").Range("A", usedrow).Value = dte
   
  'User to navigate to file
   fNameAndPath = Application.GetOpenFilename(FileFilter:="Text Files (*.txt), *.txt", Title:="Select File To Be Opened")
  'copy this 1 sheet to the current workbook and name it as the same date given by the user
  Sheets(1).Move After:=Workbooks(strMaster).Sheets(Workbooks(strMaster).Sheets.Count)
        If fNameAndPath = False Then
            Exit Sub
                Workbooks.Open Filename:=fNameAndPath




'Ask the user if there is another file if yes, then 'Ask user to Enter date
'If no more files then end


Application.ScreenUpdating = True
  
   
End Sub

[SamT]

Pamella,

I retired a while back and just dropped in on a whim. I offer this for your review and consideration.

And to show off my own variable naming style

Option Explicit

Sub Workbook_Open()
   Dim Date_Input As String
   Dim LastUsedRow_ActiveSheet As Long
   Dim Sheets_Main As Worksheet
   Dim NextRow_Sheets_Main As Long
   Dim fNameAndPath As Variant
   Dim Imported_Sheet As Worksheet
   
   Dim strMaster As String
   strMaster = Application.ActiveWorkbook.Name 'Not used and Not needed
      'ThisWorkbook always refers to the Workbook the code is saved in
      'IE, Dim SamT as String: SamT = ThisWorkbook.Name
      'It IS required if you open another Workbook and need to switch between the two.
   
    Set Sheets_Main = Sheets("Main")
    LastUsedRow_ActiveSheet = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    NextRow_Sheets_Main = Sheets_Main.Cells(Rows.Count, "A").End(xlUp).Row + 1
   

Getting_Date:
  'Ask user to Enter date
   Date_Input = InputBox("Please Enter Date: ", Default:=Format(Date, "dd-mmm-yy"))
   
   'validate input
   If Not IsDate(Date_Input) Then
      MsgBox "Im sorry. Please enter the Date again"
      GoTo Getting_Date
   End If

   'Clean it up
   Date_Input = Format(CDate(Date_Input),"dd-mmm-yy")
   
   'Double Check input
   Dim Response
      Response = MsgBox("Is " & Date_Input & " The Correct Date?", vbYesNoCancel, "Please Verify The Date")
      If Response = vbNo Then GoTo Getting_Date
      If Response = vbCancel Then Exit Sub
   Response = Nothing 'very short life variable
'End of Gettine Date Section
   
   
   'output
   Sheets_Main.Range("A", NextRow_Sheets_Main).Value = Date_Input

  'User to navigate to file
   fNameAndPath = Application.GetOpenFilename(FileFilter:="Text Files (*.txt), *.txt", Title:="Select File To Be Opened")
  
  'Import CSV file to Excel. I asSuMe the txt file is a CSV file
    

   'copy this 1 sheet to the current workbook and name it as the same date given by the user
    Set Imported_Sheet = ActiveSheet
   Imported_Sheet.Name = Date_Input
   'Move Imported_Sheet after last sheet in book
  
  'copy this 1 sheet to the current workbook and name it as the same date given by the user


End Sub

Note that each time I first referenced an Excel Object, I gave it a variable, then only used the variable in the code. This will be important when you start writing production code.

BTW, If you ever have to sort those sheets they will be in Day order, not Date order. Formatting like "yyyy, mm, dd" will place them in Date order. In compromise, you can insert a Row(s) at the top of the sheet and put in whatever Date Format you want. You can change the Sheet Object name however you want, (within limits.)

[Pamella]

Set Imported_Sheet = ActiveSheet
   Imported_Sheet.Name = Date_Input

This part doesn't copy the opened file into the current workbook

just added this

Workbooks.Open Filename:=fNameAndPath