PDA

View Full Version : [SOLVED] Prompt user for date and user to Open file



Pamella
08-12-2019, 05:35 PM
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
08-12-2019, 10:00 PM
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
08-13-2019, 02:15 AM
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