时间戳

首页 > 解决方案 > Use vbscript to input data in Excel userform and proceed

excel - Use vbscript to input data in Excel userform and proceed

问题描述

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:

  1. open the workbook in the background
  2. input the username and password in the excel userform
  3. click the submit button from the userform
  4. run the refresh all macro found in Module1

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

标签： excelvbavbscriptuserform

解决方案

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

推荐阅读