Thank you in advance for considering my post. I have an Excel file that contains a userform with a username and password field that appears everytime i open the Excel. Also, some of the worksheets are protected. Being new to vbscripting, I wanted to know how I can use it to:
So far I was able to open the workbook in the backgroung and test it without the userform but since I cannot enter the username and password, I am unable to proceed further.
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = False
oExcel.DisplayAlerts = False
oExcel.AskToUpdateLinks = False
oExcel.AlertBeforeOverwriting = False
Set oWorkbook = oExcel.Workbooks.Open("filepath\file.xlsx")
oWorkbook.RefreshAll
oWorkbook.Save
Msgbox "Excel file has been refreshed", VBOKOnly
oExcel.Quit
Set oWorkbook = Nothing
Set oExcel = Nothing
First : Your file has an extension with .xlsm
and not .xlsx
check this before proceeding !
Second : You should add this instruction in the vbscript : oWorkbook.Unprotect 1234
to unprotect the WorkBook (The password by default that i found is 1234
in your Excel file)
Third : I added On Error Resume Next
instruction to catch error if it occurs
Option Explicit
Dim Title,oExcel,oWorkbook
Title = "Use vbscript to input data in Excel userform and proceed"
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = False
oExcel.DisplayAlerts = False
oExcel.AskToUpdateLinks = False
oExcel.AlertBeforeOverwriting = False
On Error Resume Next
Set oWorkbook = oExcel.Workbooks.Open("C:\Stack\Data.xlsm")
oWorkbook.Unprotect 1234 ' The password to unprotect the WorkBook is 1234
oWorkbook.RefreshAll
oWorkbook.Save
If Err Then
MsgBox "Error Source : " & Err.Source & vbCrlf &_
"Error Description : "& Err.Description,vbCritical,Title
Else
Msgbox "Excel file has been refreshed",vbInformation,Title
End If
oExcel.Quit
Set oWorkbook = Nothing
Set oExcel = Nothing