Go Premium for a chance to win a PS4. Enter to Win

x

VB Script

36K

Solutions

20K

Contributors

VBScript (Visual Basic Scripting Edition) is an interpreted scripting language developed by Microsoft that is modeled on Visual Basic, but with some important differences. VBScript is commonly used for automating administrative and other tasks in Windows operating systems (by means of the Windows Script Host) and for server-side scripting in ASP web applications. It is also used for client-side scripting in Internet Explorer, specifically in intranet web applications.

Share tech news, updates, or what's on your mind.

Sign up to Post

Hi All,

Want to apply “Delete” and “Delete subfolders and files” permissions must be set to “Deny” for following folders:
o D:\AB\2100\Data
o D:\AB\2100\DataStore
o D:\AB\MicroSEQID\data\DataStore
o D:\ABC\2100 Data*

can we write a batch file to make the same change of DENY to all the folder in one go.
0
Industry Leaders: We Want Your Opinion!
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Hi,

I am new to working with VBA and I'm probably trying to do  something above my level of knowledge.
I want to link multiple Excel Workbooks from one folder and gather specific cells (data) in one single sheet.

I got the following code working, but it only gives the output of all files in one workbook, with the value of cells A4: C4.

Code:

Sub MergeAllWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim NRow As Long
Dim FileName As String
Dim WorkBk As Workbook
Dim SourceRange As Range
Dim DestRange As Range


' Create a new workbook and set a variable to the first sheet.
Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)


' Modify this folder path to point to the files you want to use.
FolderPath = "C:\Users\603964\Desktop\Test"


' NRow keeps track of where to insert new rows in the destination workbook.
NRow = 1


' Call Dir the first time, pointing it to all Excel files in the folder path.
FileName = Dir(FolderPath & "*.xl*")


' Loop until Dir returns an empty string.
Do While FileName <> ""
' Open a workbook in the folder
Set WorkBk = Workbooks.Open(FolderPath & FileName)


' Set the cell in column A to be the file name.
SummarySheet.Range("A" & NRow).Value = FileName


' Set the source range to be A9 through C9.
' Modify this range for your workbooks.
' It can span multiple rows.
Set SourceRange = WorkBk.Worksheets(1).Range("A4:C4")


' Set the destination range to start at column …
0
I am having a problem getting this to work correctly. I have 2 tables.

Table1:                   Table2:
tbl1ID                     tbl2ID
EventName           EventName
TStart                     TicketNo
TEnd

I made a simple form from Table1 that has the EventName, TStart, & TEnd fields on it.  I made a button labeled Generate. When you click the Generate button, I am wanting to pass the values of EventName, TStart, and TEnd from the current record in Table1 to an Event Procedure in VB that will utilize a loop starting with TStart through TEnd and create a new record for each consecutive number in Table2 and pass the EventName for each of those records.

In simple terms, I have an event in Table1 with Raffle tickets (TStart through TEnd). Instead of entering each raffle ticket number manually in Table2, I would like to use some type of coding that will automatically add the ticket numbers to Table2 along with the coinciding EventName.

This is the coding I was able to come up with but I get an error stating that "Procedure declaration does not match description of event...". I've tried a few different ways and can't get anything to work.



Private Sub Generate_Click(ByVal TStart As Integer, ByVal TEnd As Integer, ByVal EventName As Integer)

  If Table1.TStart > 0 And Table1.TEnd > Table1.TStart Then
    Do While TStart <= TEnd
      TNumber = TStart
      strSQL = "INSERT INTO Table2 (EventName, TicketNo) VALUES (EventName, TNumber);"
      TNumber …
0
Hello Team,

I have challenging task to change the windows folder permission for the individual user or group using some batch script or java or any other kind of code. Below are some rules which through which we have to apply.

The permission changes can be applied to individual non-admin user Windows accounts, or to defined Windows User group.
1) Read/write permissions must be given to the following user folder:
     -   D:\Users\TEST-USER\Data\SampleData
2) "Delete” and “Delete subfolders and files” permissions must be set to “Deny” for following folders:
     -   D:\Application\bin\data
3) Exceptions to the “Delete” and “Delete subfolders and files” must be made for the following subfolders:
    -    D:\Application\bin\datastore\record

This is how we can change folder permissions manually.
The following permissions must be set in order to allow non-administrator users to use our application software programs.

Grant Read/Write access to TEST-USER\Data\SampleData folder
1. Log into Windows as Administrator
2. Browse to the D:\Users\TEST-USER\Data\ folder
3. Rt-click on the SampleData folder, open Properties and navigate to the Security tab
4. Click Advanced
5. Click Change Permissions in the Advanced Security window
6. Click Add…
7. Search for the <COMPUTER-NAME>\Users group and click OK
8. Set the permissions for the Users: Please find the attached PermissionFile1


Set Deny delete permissions for the Users group for Application Software
1. Log…
0
I have an app that I created years ago and ran on numerous machines from Win XP to Win 7 with no issues. This app contains a progress bar control. I am trying to open the project file on a win 10 64 Pro machine, but am getting an error message when the form opens:

Line 123: Class MSComctlLib.ProgressBar of control ProgressBar was not a loaded control class.

I tried to add the component from the Project>components menu item in the project editor. A pop up to find the file comes up defaulting to the system32 directory, when I type the name of the control MSCOMCTL.OCX  in the file text box it shows up, but the file is not in that folder. If I click on it, the machine hangs. I then searched and found the correct file in the sysWOW64 folder and again tried to open it to add the component control. To my surprise, the machine again hangs...

I do not know what to do next to add this control. Any ideas???
0
I created an provider driver for SQl server using the user login for authentication , and tested successfully . But when my vb code tries to access the filedsn I created it gives me an error , saying Runtime error '-2147217843 (80040e4d)',
the code is :
Sub main()
    Dim Db As ADODB.Connection
    Dim RS As ADODB.Recordset
    Dim CMD As ADODB.Command
       Set Db = New ADODB.Connection
       Set RS = New ADODB.Recordset
       Set CMD = New ADODB.Command
    Db.CursorLocation = adUseClient
    Db.Open "Provider=SQLNCLI11;server=server-pc;Database=IFS_803_13960820;UserId=farauser;Password=tycedar"

  CMD.ActiveConnection = Db
  CMD.CommandType = adCmdStoredProc
  CMD.CommandText = "__GASKARTPEYMAN__"
  CMD.Parameters.Append CMD.CreateParameter("@PNO", adInteger, adParamInput, 10, 31529)
   
 
 Set RS = CMD.Execute
   
   
End Sub
0
I am trying to convert a .bat file to a VBScript file for convenience.  I have been able to convert all parts, except the following:

Echo DateTime=%DATE%, %TIME% > "networklocation\System.log"
Echo USERDOMAIN=%USERDOMAIN%\%USERDNSDOMAIN% >> "networklocation\System.log"
Echo COMPUTERNAME=%COMPUTERNAME% >> "networklocation\System.log"
Echo USERNAME=%USERNAME% >> "networklocation\System.log"
ipconfig >> "networklocation\System.log"
systeminfo | find /I "tImE" >> "networklocation\System.log"

Open in new window


I understand that this is appending the data to a log file, but I don't know how to write the "Echo", "ipconfig", and "systeminfo" commands in VBScript.  Any help is appreciated.
0
Hi

We use Exchange 2007 and Windows 7 PC in the network.
We use log on scripts for our staff and this is setup on the user properties in the AD- Profile-User profile-Logon script.

My predecessors had configured this VB script and when user’s logs into any PC into network with the usernames mentioned on this script. Immediately we get an alert on the administrator mail box and this very useful as which PC the user is logged in.

It was working before  and now I am not getting this alert anymore on the administrator mail box.

Please see the attached script I have only copied the logon alerts syntax from the login.vbs script to the notepad let me know if something is missing or needs amended on the script.
Or please let me know if there is any script or free utility that can do this functionality.

Thanks and any help will be great.
Logon-alert-script-.txt
0
Hello,

The attached sample has a code that breaks out information into separate workbooks based on changes in column C.  The problem I am dealing with is a sub c code exception that has been added which is missing some rows when its created and saved in a file.  For example, if the number in column c is 000060 and the manager is Washington, George, a new workbook is created named 000060A.

Based on the current code this works fine but when you open 000060A it is missing rows 1-13 that appear in the master.  The Sub c code at the bottom calls out the SR =14 which is set to the number of rows with first line after header but this is not sufficient as I mentioned earlier because its missing rows 1-13 from that master sheet.  If I change the SR to equal 1 (thinking that would grab everything), it bypasses the Sub c code.

So basically when you open 000060A the first 14 rows should look identical to opening workbooks 000030, 000002, etc. (see sample pic).  Please note that if you are going to test the existing code you'll need to provide a file path of where you want the new workbooks to be saved (Filepath = "S:\").

I've been stuck with this issue for a while and hoping some VB wizards can provide assistance.  Please provide a sample of the updated code in an attached workbook.  Thank you!
EE-Test-File.xlsm
EE-Sample-3.jpg
0
Hi everyone,

I am trying to write a script that changes the programmatic access options in outlook 2013 & 2016.
If you don't know what I am talking about see screenshot attached.
I would like to change it to "Never warn me about suspicious activity (not recommended)"

I don't have administrator rights, so I cannot change it myself.
I was wondering if a script would be able to do this without admin rights.

I have tried the below solution (renamed a .txt file as .reg), but It did not work as it does not allow me to change the registry.


Windows Registry Editor Version 5.00


[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\16.0\Outlook\Security]
"ObjectModelGuard"=dword:00000002

[HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Outlook\Security]
"PromptOOMSend"=dword:00000002
"AdminSecurityMode"=dword:00000003
"promptoomaddressinformationaccess"=dword:00000002
"promptoomaddressbookaccess"=dword:00000002


Please help!
0
Concerto's Cloud Advisory Services
LVL 5
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Dear Sir,

I cannot able to save pdf file using macro

Sub rd()

Path = "C:\Users\hitesh.dangodara\Desktop\Macro\File rename\UAN\x\1602344 Prashant NarojiFirst page - Copy.pdf"

Set app = CreateObject("Acroexch.app")
app.Show
Set AVDoc = CreateObject("AcroExch.AVDoc")
Set AForm = CreateObject("AFormAut.App") 'from AFormAPI

If AVDoc.Open(Path, "") Then
    '// write some js code on a vbs variable
   js = "f = this.addField(""newField"", ""text"", 0, [250,650,20,600]);" & vbLf _
    & "f.value = ""Peoplesoft ID""; " & vbLf _
    & "f.flatten"
     '//execute the js code
         
    AForm.Fields.ExecuteThisJavaScript js

End If

ActiveDocument.save
AVDoc.Close (True)
app.CloseAllDocs
      
Set AForm = Nothing
Set AVDoc = Nothing
Set app = Nothing
End Sub

Open in new window

0
Hello,

i want to install a printer, that i want to be named "INVOICE"

what i want : a user opens a file PDF and selects the printer named INVOICE.
The printer will print the current document 3 times (it is just one page) from a specific paper tray, in this case tray 3.

 i tried setting number of copies and paper tray setting in the basic "printing preferences" and "default printer settings" from the printer driver.  it  works for selecting paper tray, but the number of copies always get discarded.  

can this be achieved and how ?  may using a visual basic script ?
Settings-LPR-op-TCP-IP.jpg
0
Can someone explain the following vbs script code:

Set objFSO = CreateObject("Scripting.FileSystemObject")
 Set wshShell    = WScript.CreateObject("WScript.Shell")

'initialize logging
 strLogFile = wshShell.ExpandEnvironmentStrings("%TEMP%\CitrixReceiverShortcutClean.log.txt")
 Set objLogFile = objFSO.OpenTextFile(strLogFile, 8, True)
 objLogFile.WriteLine Now & ": Logging Initialized"

'get list of folders in C:\Users
 Set objFolder = objFSO.GetFolder("C:\Users")
 objLogFile.WriteLine Now & ": Got list of user folders under C:\Users"

For Each UserSubFolder in objFolder.SubFolders
	'search the users start menu for Citrix shortcuts
	objStartFolder = UserSubFolder.Path & "\AppData\Roaming\Microsoft\Windows\Start Menu\Programs"
	objLogFile.WriteLine Now & ": Searching folder: " & objStartFolder
	If objFSO.FolderExists(objStartFolder) Then
		Set objSearchFolder = objFSO.GetFolder(objStartFolder)
		Set colFiles = objSearchFolder.Files
		For Each objFile in colFiles
		  If InStr(objFile.Name, ".lnk") Then
			IsCitrixShortcut objSearchFolder.Path & "\" & objFile.Name
		  End If
		Next

		ShowSubfolders objFSO.GetFolder(objStartFolder)

		' now search the users desktop for Citrix shortcuts
		objStartFolder = UserSubFolder.Path & "\Desktop"
		objLogFile.WriteLine Now & ": Searching folder: " & objStartFolder

		Set objSearchFolder = objFSO.GetFolder(objStartFolder)
		Set colFiles = objSearchFolder.Files
		For Each objFile in colFiles
			If InStr(objFile.Name, ".lnk") Then
	

Open in new window

0
We have a process that automatically starts when a user logs on.  If we do a switch user, that process stays active under the original user and will not run for the new user. Does anyone know how to kill a specific process named "killme" when a user locks their workstation, without having to add a scheduled task for every possible individual who may log on to that particular machine?  Thanks
0
Hello!!

I hope u can help with my problem, please.

I want to create a table that has this format ( Test2.png)
but
I only get this result (Test1.png)

This is the code:

------------------------------------------------------------------------------------------------

On Error Resume Next
 
Set objSysInfo = CreateObject("ADSystemInfo")
 
strUser = objSysInfo.UserName
Set objUser = GetObject("LDAP://" & strUser)
 
strName = objUser.FullName
strTitle = objUser.Title
strFName = objUser.FirstName
strLName = objUser.LastName
strInfo = objUser.info
strMobile = objUser.Mobile
strWebAddress = ""
Logo = "\\opdc01\Logo\1.jpg"
Logo2 = "\\opdc01\Logo\2.jpg"
strDepartment = objUser.Department
strAddress = objUser.StreetAddress
strCity = objUser.l
strState = objUser.St
strco = objuser.co
strPostalCode = objUser.PostalCode
strCelular = objuser.homePhone
strPhone = objUser.telephoneNumber
strExt = objuser.ipPhone
strFax = objuser.faxnumber
strEmail = objuser.mail
strExtend = objuser.extensionAttribute3 
Set objWord = CreateObject("Word.Application") 
Set objDoc = objWord.Documents.Add()
Set objSelection = objWord.Selection
Set objRange = objDoc.Range()
objDoc.Tables.Add objRange,1,3
Set objTable = objDoc.Tables(1)
Set objEmailOptions = objWord.EmailOptions
Set objSignatureObject = objEmailOptions.EmailSignature
Set objSignatureEntries = objSignatureObject.EmailSignatureEntries
objTable.Range.Font.Size = 20
objTable.Range.Style = "Table Contemporary"
objTable.Range.Bold = 

Open in new window

0
I have been researching various methods.One of interest, is to open the printer queue,sort and copy all files in a folder to the printer queue.This eliminates the looping.Can any one provide vba to accomplish this?

Thanks
0
As per client requirement, I have to remove blank lines from reply message in Email Signature.
1)

1.png
2) After clicking on OutLook-->Formate Text --> Plain Text

2.png
Here I want to remove these 5 lines using VBScript.

If Not BannerImage = "" And Not Left(BannerImage, 8) = "Excluded" Then
           debugLog("Setting Marketing Banner")

       selection.TypeParagraph()
       Set Logo = selection.InlineShapes.AddPicture(BannerImage)
       document.Hyperlinks.Add logo.Range, BannerURL
       selection.TypeParagraph()
       End If   

   selection.TypeParagraph()
   selection.Font.Name = "Arial"
   selection.Font.Size = "7"

If recordset.Fields("co") <> "Netherlands" Then

           debugLog("Setting Facebook Logo")

   Set Logo = selection.InlineShapes.AddPicture(FacebookLogo)
   document.Hyperlinks.Add logo.Range, FacebookURL
   Logo.Height = 19
   Logo.Width = 19

End If


       debugLog("Setting LinkedIn Logo")

   Set Logo = selection.InlineShapes.AddPicture(LinkedInLogo)
   document.Hyperlinks.Add logo.Range, LinkedInURL
   Logo.Height = 19
   Logo.Width = 19

       debugLog("Setting Twitter Logo")

   Set Logo = selection.InlineShapes.AddPicture(TwitterLogo)
   document.Hyperlinks.Add logo.Range, TwitterURL
   Logo.Height = 19
   Logo.Width = 19

       debugLog("Setting YouTube Logo")

   Set Logo = selection.InlineShapes.AddPicture(YouTubeLogo)
   document.Hyperlinks.Add logo.Range, YouTubeURL
   Logo.Height 

Open in new window

0
Hello,

I have a vbscript I have finished and pushing out via gpo.

I want to convert the vbs to html. How do I go about doing this?
0
I'm wanting to create a batch file that will check for the existence of a device name like 'PowerMIC%' before running a program.  I'm able to run the command  " wmic path CIM_LogicalDevice where "Name like 'PowerMIC%'" get /value " which shows lots of data if the device is present or " No Instance(s) Available. " if the mic isn't plugged into the USB port ....

Any thoughts on easily doing this .....   I'm not very proficient at batch files  .....

Joel
0
Technology Partners: We Want Your Opinion!
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Hello,

I have an script which gives me output of memory, CPU and disk utilization in excel. Whenever I run the script it gives output in different new file.
I want report should get generated in single file for multiple run times. So that I can take average utilization report for all these items.

Is there any way to modify the script to append this data in single file?
0
Hello,

Seeking assistance for developing a VB code to help automate a process.  The attached spreadsheet has an existing code that breaks into smaller, individual workbooks based on changes in the ID (column C).  This part of the code is running as expected.

Based on an update I need an update to the code that will do the following BEFORE the majority of the data is split up into separate workbooks:

In column C, if the number is 000060 and the manager (column K) is Washington, George, take all applicable rows and place in a new workbook called 000060E.

In column C, if the number is 000060 and the manager (column K) is Washington, Martha, take all applicable rows and place in a new workbook called 000060A.

The selected rows in both conditions would be removed from the master file so they do not appear when the main code to split out the rest of the IDs is ran.  The ID and the manager must match.  Any remaining 000060 that do not match the conditions mentioned would remain on the master file.  So once the overall code is ran I would end up with a workbook 000060, 000060E and 000060A saved in a specified network folder.

Way beyond my limited coding skills and hoping the VB code ninjas can help.  Thank you for any assistance!
EE-Sample.xlsm
0
I want to compare one excel workbook with many excel workbook and show out the matched parameters.Please help me out of this. I will be so thankful to you.
0
Hi,

We have a macro which will use many sheets to prepare a final report . However at the end of the completion, it should trigger a mails as completed. How to add that ?

Thanks
Venkatesh.
0
I'm trying to do a fun, silly project but my programming skills still aren't spectacular...

I'm trying to create a vbscript (really, a vbscript compatible function, but that I can work out if I get a working script).

It looks like I can do this easily in Word, but that's not the goal.

I see some problems with the code below, but I'm not sure how to address them.  For example: If I'm interpreting things correctly, Synonyms.PartOfSpeechList is an object type but I don't know how to do deal with that in the VBScript.  Additionally, I'm checking for what I believe are constants in wdVerb, wdNoun, etc - but I don't know how to obtain their values... I tried a For Each loop (in the code below) but that didn't work...)

Any suggestions?

Option Explicit
 
Dim MSWord 
Dim Synonyms
Dim ThisWordIsType
Dim WordType 
Dim TestWord
Dim PartOfSpeech

Dim CheckWordType 
Dim Meanings 

'Documentation references:
'https://msdn.microsoft.com/en-us/library/microsoft.office.interop.word.wdpartofspeech.aspx?f=255&MSPPError=-2147217396
'https://msdn.microsoft.com/en-us/library/microsoft.office.interop.word.synonyminfo.partofspeechlist.aspx
'https://msdn.microsoft.com/en-us/library/microsoft.office.interop.word.synonyminfo_members.aspx

TestWord = "Jump"

Set MSWord = CreateObject("Word.Application")

Set Synonyms = MSWord.SynonymInfo(TestWord, 1033)

WScript.Echo "Meanings found: " & CStr(Synonyms.MeaningCount)
     
If Synonyms.MeaningCount <> 0 Then
	Meanings = 

Open in new window

0
I have a code which merges all excel files data in a single file. Now i want to add the file name as a column to each row or to a first row in each file's data.

 what should the commands added to to it, please help. More so please help in making the file extension dynamic.

Sub MergeDataFromWorkbooks()
'DECLARE AND SET VARIABLES
Dim wbk As Workbook
Dim wbk1 As Workbook
Set wbk1 = ThisWorkbook
Dim Filename As String
Dim Path As String
Path = "C:\Users\milans\Desktop\Test excel combine work\" 'CHANGE PATH
Filename = Dir(Path & "*.xls")
'--------------------------------------------
'OPEN EXCEL FILES
Do While Len(Filename) > 0 'IF NEXT FILE EXISTS THEN
Set wbk = Workbooks.Open(Path & Filename)
wbk.Activate
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Book1.xlsm").Activate
Application.ScreenUpdating = False
Dim lr As Double
lr = wbk1.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Sheet1").Select
Cells(lr + 1, 1).Select
ActiveSheet.Paste
wbk.Close True
Filename = Dir
Loop
MsgBox "All the files are copied and pasted in Book1."
End Sub
0

VB Script

36K

Solutions

20K

Contributors

VBScript (Visual Basic Scripting Edition) is an interpreted scripting language developed by Microsoft that is modeled on Visual Basic, but with some important differences. VBScript is commonly used for automating administrative and other tasks in Windows operating systems (by means of the Windows Script Host) and for server-side scripting in ASP web applications. It is also used for client-side scripting in Internet Explorer, specifically in intranet web applications.