Hi there,
I'm trying to merge two blocks of code I've found online but having a bit of difficulty.
I am looking to set a powerpoint doc to automatically run a marco when opened. The macro should open an excel workbook and record the users info before saving and closing it.
Both blocks of code (one to open the wb and the second to record user details) work independently but not when merged. The issue seems to be around selecting cell A1 once the workbook (User Log record) is open.
I'm trying to merge two blocks of code I've found online but having a bit of difficulty.
I am looking to set a powerpoint doc to automatically run a marco when opened. The macro should open an excel workbook and record the users info before saving and closing it.
Both blocks of code (one to open the wb and the second to record user details) work independently but not when merged. The issue seems to be around selecting cell A1 once the workbook (User Log record) is open.
VBA Code:
Sub macro2()
'BLOCK 1 - OPEN WORKBOOK
Dim xlApp As Object
Dim xlBook As Object
Const strWorkbookName As String = "C:\Users\John\OneDrive\Documents\Macros\User Log record.xlsx" 'the name and path of the workbook
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err Then
Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo 0
Set xlBook = xlApp.Workbooks.Open(FileName:=strWorkbookName)
xlApp.Visible = True
lbl_Exit:
Set xlApp = Nothing
Set xlBook = Nothing
'Exit Sub
' BLOCK 2 - RECORD USER DETILS
Range("A1").Select
ActiveCell.End(xlDown).Offset(1, 0).Select
ActiveCell.Value = Application.USerName
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Template 2"
ActiveCell.Offset(0, 1).Select
ActiveCell = Date
ActiveCell.Offset(0, 1).Select
ActiveCell = Time
ActiveWorkbook.Close SaveChanges:=True
End Sub