I have borrowed this code from someone but I can't remember who. It's working great, but I just can't figure out where to name and save the excel workbook. I would like to automate this so it will run and then automatically save the workbook and close the process. This is kind of long, but any help I can get will be terrific. I'm new to the vba in excel coding.
ThisWorkbook code
Private Sub Workbook_Open()
'call set reminder
SetReminder
Dim strDir As String, strFileName As String
Dim wbSourceBook As Workbook
Dim wbWriteBook As Workbook
Dim wsWriteSheet As Worksheet
strDir = "Folder where I have the csv files" 'specify folder to search
strFileName = Dir(strDir & "*.csv")
Set wbWriteBook = Workbooks.Add
Do While strFileName <> ""
Set wbSourceBook = Workbooks.Open(strDir & strFileName)
Set wsWriteSheet = wbWriteBook.Sheets.Add
wsWriteSheet.Name = strFileName
wbSourceBook.Sheets(1).UsedRange.Copy wsWriteSheet.Range("A1")
wbSourceBook.Close False
strFileName = Dir
Loop
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Stop the procedure automatically on closing the workbook
On Error Resume Next
'Call StopSetReminder to stop Application.OnTime
StopSetReminder
'Save workbook before closing
ThisWorkbook.Save
End Sub
Module1 Code
Public dTime As Date
Sub SetReminder()
dTime = Now + TimeValue("00:00:05")
'procedure named SetReminder will autmatically run, at the sheduled time interval, with the OnTime Method.
Application.ontime dTime, "SetReminder"
'Close the workbook at the specified time:
If Time = TimeSerial(3, 0, 0) Then
CloseWorkBook
End If
'If Day(Now) = 1 Then
'Application.ontime TimeValue("3:00:00"), "Workbook_Open"
'End If
End Sub
Sub StopSetReminder()
'Stop the ontime procedure
Application.ontime dTime, "SetReminder", , False
End Sub
Sub CloseWorkBook()
'Close the process
On Error Resume Next
StopSetReminder
ThisWorkbook.Save
ThisWorkbook.Close
End Sub
Bookmarks