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
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