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

Hello my peers,

I need your guidance and help please to amend the below code that I to include in additional filter requirements and to loop through all files in a folder and complete the below code to every workbook.

Code that i have tried amending is: ws.Cells(1, icol) = "SEL"

I need to add  "STORE" & "CATEGORY"

and finally to loop through all files in folder and complete the below code.

SO END RESULT WOULD BE:

Data split into new worksheet with column data for  "SEL" & "STORE" & "CATEGORY" and finally to loop through all files in folder and complete the below code.




Sub parse_data()
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
vcol = 4
Set ws = Sheets("Sheet1")
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = "A1:L1"
titlerow = ws.Range(title).Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "SEL"
For i = 2 To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then

Open in new window

0
What does it mean to be "Always On"?
LVL 4
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

I have a workbook with multiple rows. Each row shows the name of who the monitor is assigned to. Some users have several monitors. I want to create a second sheet in the workbook which simply counts how many monitors each user has. Where there is a "." in the user name this means it is assigned to a location not a person. I need a total count for these too. Please see attached sample input and out.

 (the number of rows will vary, it will be run monthly to get stats)
0
Hi All,

Can anyone here please share some tips or steps in Group Policy in how to create the Outlook Email signature for each people in different OU ?

For Example:

Domain.com/Users/Accounting --> Email Signature Template ACC1.
Domain.com/Users/IT --> Email Signature Template IT1.
Domain.com/Users/Marketing --> Email Signature Template MRK1.

How to do that based on each OU ?

Note, the users are using various different OS and Office product like:
Operating System: Windows 7 and Windows 10
MS Office: 2010, 2013 and 2016 (some will use 64 bit, but majority is 32 bit)

Any help and guidance will be greatly appreciated,

Thanks,
0
how to increase characters in the kutool vbscript
0
hi,

I need to find the today date is business day or not.
if today days is saturday then i want to add 16 businnes date after, if its sunday i want to add 15 date after.

kindly help on this by using vb script.

Regards,
Baskar P
0
Hello,

I use a customized Outlook form that runs VB Script.  I have a command button that opens up a Word template and prints it to a PDF file.  What happens though is when printing, the save PDF dialog box comes up.  I don't want that.  I want it to save it as a PDF automatically without the dialog box coming up.  Let's just say C:\test.pdf.  Here's the code that I currently use.

Sub CommandButtonShipLabelUSPS_Click()

On Error Resume Next

Dim strCurrentPrinter' As String

	Set objDoc2 = GetWordDocLabel("\\TGPS13VM1\drawing$\Jobs\Task_Templates\Ship-Ship Label USPS.dot")
    	Call FillFieldsShipLabel(objDoc2)
    	objDoc2.Application.Options.PrintBackground = False
    	objDoc2.Application.DisplayAlerts = False

if Item.UserProperties("PrintHold") = True then
  	Set objDoc2 = Nothing
  	Set strCurrentPrinter = Nothing
  	Set objWord2 = Nothing
	Exit Sub
end if

    	strCurrentPrinter = objDoc2.Application.ActivePrinter ' store the current active printer
    	objDoc2.Application.ActivePrinter = "Adobe PDF" ' change to another printer

    	objDoc2.PrintOut ' print the active sheet
	Call RestoreActivePrinter

	objDoc2.Application.DisplayAlerts = True
    	objDoc2.Application.Options.PrintBackground = True

objDoc2.Close wdDoNotSaveChanges
objWord2.Quit wdDoNotSaveChanges

	Set objDoc2 = Nothing
  	Set strCurrentPrinter = Nothing
  	Set objWord2 = Nothing

End Sub

Open in new window




Thank you,
Joel
0
My previous application in VB6 would create a record using the code below:

Module:

Module basDeclarations

Public Const maxTables = 20

Structure Gametype
    <VBFixedString(6)> Public memberID As String
    Public TableID As Integer
    Public StartTime As Date
    <VBFixedString(1)> Public Occupied As String
End Structure

End Module

Procedure:

Public Sub CreateCurrentGamesFile()
    Dim OneGame As Gametype
    Dim TableNumber As Integer
    Open app.path & "\CurrentGames.dat" For Random As #1 Len = Len(OneGame)
    For TableNumber = 1 To maxTables
        OneGame.memberID = ""
        OneGame.TableID = TableNumber
        OneGame.Occupied = "N"
        Put #1, TableNumber, OneGame
    Next TableNumber
    Close #1
End Sub
End Class

How can I convert this block of code in vb.net to create new records as a random access file? Or Is there a better way to store records which can be easily saved and deleted in vb.net? Thanks

Please provide comments on your code
0
I am trying to paste excel sheet table in oulook body but i am getting error.
Runtime error 91Object varialbe or with block variable not set.

below are the code

Sub CreateMail()
Dim objOutlook As Object

Dim objMail As Object

Dim rngTo As Range

Dim rngSub As Range

'Dim rngMessage As Range

Dim rngCc As Range
 
Dim RngCopied As Range
 

Set objOutlook = CreateObject("Outlook.Application")

Set objMail = objOutlook.CreateItem(0)



With ActiveSheet

Set rngTo = .Range("B1")

Set rngCc = .Range("B2")

Set rngSub = .Range("B3")

'Set rngMessage = RangetoHTML(RngCopied)

'rngMessage.Copy


End With

With objMail

.To = rngTo.Value

.Cc = rngCc.Value

.Subject = rngSub.Value

.Body = RangetoHTML(RngCopied)

.Display 'Instead of .Display, you can use .Send to send the email _

End With

On Error GoTo 0

Set objOutlook = Nothing

Set objMail = Nothing

Set rngTo = Nothing

Set rngCc = Nothing

Set rngSub = Nothing

'Set rngMessage = Nothing

'Set RngCopied = Nothing

End Sub

Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2010
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook
 
    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
 
    'Copy the range and create a new workbook to past the data in
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial 

Open in new window

0
I'm writing a vbscript wherein auotamically create a folder that rename as date/time stamp. I create vbscript wherein the telnet output file save as .text then save on the folder that automatically created. This is my script on the telnet output file.

Option Explicit

On Error Resume Next

Dim WshShell

set WshShell=CreateObject("WScript.Shell")

WshShell.run "cmd.exe"

WScript.Sleep 1000

'Send commands to the window as needed - IP and commands need to be customized

'Step 1 - Telnet to remote IP'

WshShell.SendKeys "192.168.1.1 -f C:\log.txt"
WshShell.SendKeys ("{Enter}")
WScript.Sleep 1000


'Step 2 - Issue Commands with pauses'

WshShell.SendKeys ("password")

WScript.Sleep 1000

WshShell.SendKeys ("{Enter}")

WScript.Sleep 500
WshShell.SendKeys ("Enable")
WshShell.SendKeys ("{Enter}")
WScript.Sleep 500

WshShell.SendKeys ("password")
WshShell.SendKeys ("{Enter}")
WScript.Sleep 500

WshShell.SendKeys ("terminal length 0")
WshShell.SendKeys ("{Enter}")
WScript.Sleep 500

WshShell.SendKeys ("show mac address-table vlan 13")
WshShell.SendKeys ("{Enter}")
WScript.Sleep 1000

WshShell.SendKeys "exit"
WshShell.SendKeys ("{Enter}")
WScript.Sleep 500

WshShell.SendKeys ("{Enter}")
WScript.Sleep 500

Open in new window

0
I am working on a generic VB ASP page for all our sites that will toss up the status code and error message, works great in testing directly to it or redirecting to it...however if I leverage the page as a file, it doesnt execute any of the asp vb code

I wrote it generically so that it could be placed on any of our corporate sites and offer redirection to that applications home root or our corp status page...it uses the following in the code
Response.Status
Response.Write
Request.ServerVariables("SERVER_NAME")

Code snips

      <meta http-equiv="refresh" content="500;url=https://<%Response.Write(Request.ServerVariables("SERVER_NAME"))%>"/>


      Dim StatusCode
      Dim StatusMessage
      StatusCode=split(response.status," ")(0)
      StatusMessage=Right(response.status,(len(response.status)-len(StatusCode)))%>
      <h1 style="font-size: 170px;"><%Response.Write(StatusCode)%></h1>
    <h2><%response.write(StatusMessage)%></h2>
    <p style="width: 400px; margin-left: auto; margin-right: auto; margin-top: 15px;">
        Sorry, but the page you are looking for has not been found. Try checking the URL for an error, then hit the refresh button on your browser. Click the button below to navigate to the <%Response.Write(Request.ServerVariables("SERVER_NAME"))%>:    
    </p>
    <a class="btn btn-primary m-t" href="https://<%Response.Write(Request.ServerVariables("SERVER_NAME"))%>">Home</a>
      <p> Error Code: <%Response.Write(Response.Status)%></P>
0
On Demand Webinar: Networking for the Cloud Era
LVL 9
On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Imports System.Data.OleDb

Public Class Reports
    Dim myDA As OleDbDataAdapter
    Dim myDataSet As DataSet

    Private Sub TextBox3_TextChanged(sender As Object, e As EventArgs) Handles txbdat.TextChanged

    End Sub
    Public Sub DatagridData()
        Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Lenovo\Desktop\SAD\SadDatabase.accdb")
        Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Room1Report", con)
        con.Open()
        myDA = New OleDbDataAdapter(cmd)

        Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(myDA)
        myDataSet = New DataSet()
        myDA.Fill(myDataSet, "Room1Report")
        Dgvrep.DataSource = myDataSet.Tables("Room1Report").DefaultView
        con.Close()
        con = Nothing
    End Sub
    Public Sub DatagridGetData()
        Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Lenovo\Desktop\SAD\SadDatabase.accdb")
        Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [RegisteredSA] WHERE [IDnumber] = '" & Login.txbidn.Text & "' AND [Password] = '" & Login.txbpas.Text & "'", con)
        cmd.Connection.Open()
        Dim reader As OleDb.OleDbDataReader = cmd.ExecuteReader()
        If reader.Read = True Then
            txbfir.Text = reader.Item("Firstname")
            txblas.Text = reader.Item("Lastname")

        End If
    End Sub
    Private …
0
What is the VB script to copy data from one database/table and paste to another database/table?
0
How do you write a SQL Statement/Query on an SQL Server,that will look for the "PrintEvent" from a label application like Easy Label, Bartender, etc, and copy the data from the fields that were printed and then write that data to the Database?
0
I have a Macro Enabled Excel Sheet setup to retrieve API data from Goformz but For some reason when it get's to a record that has a blank latitude and longitude the script stops and doesn't retrieve any more records.

Question 1:  What do i need to add to the "latitude" and "location" code so if the record is blank add the word "NA" to the cell and continue running macro for remaining records??

Question 2: What do i need to add to the "lastupdatedate" code so the date format looks like this "dd/mm/yyy 00:00" and not
2017-08-09T01:36:39.0000000+00:00 ??

Question 3: When the tablestyle is applied it is applying it to the entire columns, instead of just only the columns that contain data. What do i need to change in my tablestyle code ??


TableStyle Code looks like this:

Dim rList As Range

On Error Resume Next
With Worksheets("Completed Forms")
    If .ListObjects.Count > 0 Then
        .ListObjects ("Table3")
        Set rList = .Range
        .Unlist
    End If
End With

Sheets("Completed Forms").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:G:G"), , xlYes).Name = _
        "Table3"
    Range("Table3[#All]").Select
    ActiveSheet.ListObjects("Table3").TableStyle = "TableStyleMedium2"

Open in new window


Getting the Data from Goformz Code looks like this:

Sheets("Completed Forms").Cells.Clear
Dim http As Object,

Open in new window

0
Hi all,

I created a macro script for AS400, basically with the comand "autECLSession.autECLPS.SendKeys "word" / "right" / etc...

This macro script runs at perfection but the error comes when there is a variance in the screen, because sometimes depending of the value introduced, it jumps a box with information (that I have to introduce Y/N (yes/no)), or just, sometimes, the values introduced are incorrected (because exceed the limit of characters available for a string), and the macro starts to fall and collapse the console.

My question is the following: there is any method that when occur an error in the macro, and appears the X in the status bar, the macro stops at that point?)

Kindest regards.
0
I had this question after viewing Script to import contacts from Excel to Outlook.

with this script is it possible to copy contact to different folder in My Contacts?
0
See slimmed down code. I'm essentially creating a list of items (printers) along with a dynamically created unique radio button ID, and then I'd like to be able reference said Radio ID in order to toggle the Checked between True/False in Sub(SetDefaultPrinter). Why? Because using Add Devices / Search is too hard for some of our users, hence, a cute little GUI. Why dynamic? Because I have multiple separate networks and I'd prefer the script to adjust itself as needed.
<html>
<head>
<title>My HTML application</title>
<HTA:APPLICATION
  APPLICATIONNAME="My HTML application"
  ID="MyHTMLapplication"
  VERSION="1.0"/>
</head>

<script language="VBScript">

Public jj, strPrinters, strModels, strLocations

Sub Window_OnLoad
    strPrinters = Array("Printer1", "Printer2")
    strModels = Array("HP Color LaserJet 4525", "HP Color LaserJet 4525")
    strLocations = Array("Room 1", "Room 2")

    jj = UBound(strPrinters)
    
    Call OnClickGo()
End Sub

Sub OnClickGo()
    DataArea1.InnerHTML = ""
    For i = 0 To jj
            DataArea1.InnerHTML = DataArea1.InnerHTML & "<BR><font style=color:green;font-weight=bold;>" &_
              "<input type=""" & "radio""" & " name=""" &_
              strPrinters(i) & "Radio""" & " id=""" & "Radio" & i & """" &_
              " title=""" & "Clicking here will set " & strPrinters(i) & " as default printer.""" &_
              " onclick=""" & "SetDefaultPrinter(" & i & ")""" & " onmouseover=""" & "Pointer""" &_
              " 

Open in new window

0
I had this question after viewing Modifying PowerShell to list some Event logs with certain username ?.

People,

I wonder if it is possible to modify the below PowerShell script to dump all information from the Event ID 4625 ?

The below script was created by one of the experts here but it is just for Event ID 4624.

$LogonType = @{
	[uint32]2 = 'Interactive'
	[uint32]3 = 'Network'
	[uint32]4 = 'Batch'
	[uint32]5 = 'Service'
	[uint32]7 = 'Unlock'
	[uint32]8 = 'NetworkCleartext'
	[uint32]9 = 'NewCredentials'
	[uint32]10 = 'RemoteInteractive'
	[uint32]11 = 'CachedInteractive'
}

Get-ADComputer -LDAPFilter "(&(objectCategory=computer)(userAccountControl:1.2.840.113556.1.4.803:=8192))" | ForEach-Object {
    "Processing $($_.DNSHostName) ..." | Write-Host
	Get-WinEvent -ComputerName $_.Name -FilterHashTable @{LogName="Security"; ID=4625; Data="FirstName.LastName"} -MaxEvents 100 | ForEach-Object {
		New-Object PSObject -Property ([ordered]@{
			MachineName = $_.MachineName
			TimeCreated = $_.TimeCreated
			User = $_.Properties[5].Value
			Domain = $_.Properties[6].Value
			LogonType = $_.Properties[8].Value
			LogonTypeString = $LogonType[$_.Properties[8].Value]
			SourceIP = $_.Properties[18].Value
			SourceName = (Resolve-DnsName -Name $_.Properties[18].Value -ErrorAction SilentlyContinue).NameHost
			Keywords = $_.KeywordsDisplayNames -join ";"
		})
	}
} | Export-Csv -Path C:\TEMP\User-LockedOutSource.csv -NoTypeInformation -UseCulture

Open in new window


More Information about Event ID: 4625: https://www.ultimatewindowssecurity.com/securitylog/encyclopedia/event.aspx?eventID=4625
0
I have a spreadsheet that I enter a list of options then it generates a list based on the options.  To simplify I've added the spreadsheet so you can see what I'm trying to get done.
0
Independent Software Vendors: We Want Your Opinion
Independent Software Vendors: 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 ,

Good Day!

Please help me with my simple project. I want to show the picture on the cell from picturebox when I click the PRINTrecord.
Your help will be so much appreciated.

Thanks.
Employee-Information-System.xlsm
0
Hello,

This is a follow-up to an earlier question posted here:

Combine multiple sorted Excel worksheets to a single summary worksheet which is also sorted

The main question in that thread was:
Is there an Excel formula (or formulas) which will merge data from multiple worksheets into a single summary worksheet and keep the combined rows sorted?

Please refer to the earlier thread for detailed examples and screenshots.

The questions below refer to the solution in the previous thread which is also included here for convenience:

Sub CombineSheets()
    ' loop all sheets and combine
    For Each ws In Sheets
        ws.Select
        ' if 1st sheet, clear first
        If (ws.Name = "Combined") Then
            Range("B4:G4").Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.ClearContents
        Else
            ' for other sheets, copy and paste into first one
            Range("B4:G4").Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.Copy
            Sheets("Combined").Select
            
            Range("B4").Select

Open in new window

0
HiMartin,
I had this question after viewing How to create dropdown with autofill/autocomplete in Excel.

Can i use the code you provided  in the the "this workbook" or module in Vb? I have so many sheets and i though its better to do it one time  rather that pasting this to all sheets in VBE.

If it also not too much to ask, may I add in my question; if this code can be done that any string to be inputted in the combo box will provide suggestion options from the data validation drop down  list (really like a google type behavior)?

Thanks,
Reggieneo
0
We have a third party software where our scheduled reports are ran through to recipient's outlook email addresses.
Is there a way to setup a rule and have a script change the subject line to the attachment name on incoming emails?    I do not need to save the attachment.      
Any advice would be greatly appreciated.
Brenda
0
I have a dilemma. I have a MS 2000 Access database which I manage for a weighbridge. There is a VB Exe that is called from a form, in order to receive data from a weighbridge display.  The program currently works perfectly in the MDB and even though they have been upgraded to MSOffice 2016 including Access I have retained the old MDB file (which opens with 2016?) and it still works perfectly however I have not attempted to make any changes in case something goes wrong in the interim.
I have the VB Project code for the VB Exe on my old XP computer and can make a new Exe from it but I can't "reference" Access 2016 hence my Exe is no good in the new Access 2016 DB (.accdb).
On my computer I am using Windows 10 and MS Office 2016 and have successfully converted their database to 2016 via my Access 2010 and I use this for testing purposes on the weighbridge but unfortunately the EXE does not call the display data because I think the problem is, I cannot "reference" 2016 in the VB Project on the XP computer because I don't have Access 2016 on the XP computer.
I don't have my original VB6 disks (bought back in 2000) so I can't load VB6 onto my good computer.  I have a copy of Windows 2007 which I could load onto my old XP computer but because it's a new set and not an upgrade I believe I would lose any programs  currently installed, (only worried about VB6)
I understand I can download Access 2016 drivers for free but cannot find any discussion regarding Access 2016 drivers and …
0
I am looking for few things to be automated in AD- through script or any other better available solution

1.To Send email notification after AD user creation
2. To send reminder Email on password expiry  (before 1 Day, 10 Day's, 20 Day's)
3. Generate password automatically  and then send it on email

Thanks in Advance for prompt support .
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.