VB Script

37K

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

I have a workbook that has 2 sheets and data in columns A-D what im trying to do is in column A on both sheets there is a unique 4 digit number of which is duplicated numerous times and then changed to a new one.

Im trying to somehow automate it so it sets up the filter in column A in both sheets and then selects the first number to filter it out in sheet 1 and then filter out same first number and data in sheet 2.

I then  need it to do an auto sum down column D of both sheets and if both total amounts agree (totals in sheet 1 & 2)  then thats fine leave it alone and move onto the next unique number and repeat function.  Dont worry about saving autosum.

If both total amounts dont agree I need it to compare data against each other in each sheet.  If the data's in sheet 1 but not sheet 2 then I need that row of data highlighting in sheet 1.

If all the data in sheet 1 & 2 are both present but still dont balance I then need it to look in sheet 2 only and look for duplicated entries. As far as I mean by duplicated entries all the data in columns A-D .

I then need any duplicated entries highlighted too.


Any Ideas?
0
JavaScript Best Practices
LVL 13
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

I tried to create view in query but it's throwing up error ,.this is view i created in query create view Search as (select ----)
error - syntax error in create table statement
can someone tell me how can we create view in ms access? i have seen that we can do that using vb code
but no idea how to do that and how can we execute that
i want view as record  source for my form so kindly let me know how this can be done
1
Using a macro in Excel, or perhaps VB Script, or some other external script.
When the A2 field changes, then fields B2, C2,  and F2 should all be cleared.
What script will help me to do this?
0
I need a PS that will check a txt file of servers and verify if the Spooler service is stopped, if so start the service.  I need this asap.

Thanks!
1
Hello,

I have a script which loads a text file into a variable and then uses the content for an email message.  I would like to search the variable on a specific location and then run a SELECT CASE statement against it and replace the variable based on what is found.  A sample of the text file read is as follows:

{
	"service_key": "87d50a3aa0bc4asdfa3gxcbd348",
	"incident_key": "gpon1.town Reboot",
	"event_type": "trigger",
	"description": "gpon1.town has rebooted",
	"client": "SolarWinds",
	"client_url": "http://orion:8787/Orion/View.aspx?NetObject=N:336",
	"details": {
		"Last Boot Time": "Thursday, November 15, 2018 10:38 PM"
	}
}

Open in new window


The portion of code which reads the text file into a variable is as follows:

			
Set AlertFileContent = CreateObject("ADODB.Stream")
AlertFileContent.CharSet = "utf-8"
AlertFileContent.Open
AlertFileContent.LoadFromFile(AlertFile)
PostBody = AlertFileContent.ReadText()
PostBody = Replace(PostBody, "\", "\\")
PostBody = Replace(PostBody,vbCr,"")
PostBody = Replace(PostBody,vbLf,"")
AlertFileContent.Close

Open in new window


So essentially I want to search the PostBody variable for the string the follows "service_key" between the "" and based on what is found replace it with 1 of 3 different strings.  Can anyone draft some code that would accomplish this:

Select case service_key
  case "string 1"
      New_Service_Key = "asdfasdfasdfasdf"
  case "string 2"
      New_Service_Key = "123142352"
  case "string 3"
      New_Service_Key = "sdfger3434"
  case else
      New_Service_Key = "dvg4556ssd"
End Select

PostBody = Replace(PostBody, service_key, New_Service_Key)

Open in new window

0
What is the command to use in the powershell to verify the process is running after it is terminated?  I need to verify what the process is doing after it is stopped.

$credential = Get-Credential
$processName = 'notepad.exe'

Start-Transcript -Path 'C:\Temp\StopProcess.log'
Get-Content -Path C:\Temp\Computers.txt | ForEach-Object {
	Write-Host "Processing '$_' ..." -NoNewline
	Try {
		If (Test-Connection $_ -Count 1) {
			If ($process = Get-WmiObject -Query "Select * From Win32_Process Where Name=`"$($processName)`"" -ComputerName $_ -Credential $credential -ErrorAction Stop) {
				$result = $process.Terminate()
				If ($result.ReturnValue -eq 0) {
					Write-Host ' process terminated.' -ForegroundColor Green
				} Else {
					Throw "Process.Terminate() failed with exit code $($result.ReturnValue)!"
				}
			} Else {
				Write-Host ' process not found.' -ForegroundColor White
			}
		} Else {
			Write-Host ' offline' -ForegroundColor Yellow
		}
	} Catch {
		Write-Host "Error: $($_.Exception.Message)" -ForegroundColor Red
	}
}
Stop-Transcript

Open in new window

1
I want to add table in email sent by VBA code from access . Below is the code as of now.
MyStr1 = StrComp(.Fields(11), "Cisco")
If MyStr1 = 0 Then
Set objBP = email.AddRelatedBodyPart("C:\images\cisco.jpg", "cisco.jpg", 1)
email.Fields.Item("urn:schemas:mailheader:Content-ID") = "<cisco.jpg>"
email.Fields.Update
email.HTMLBody = "<html><img src=""cisco.jpg""><br><br>Dear " & .Fields(3) & ",<br><br> " & str1 & " in <b> " & .Fields(5) & " days</b>.<br><br>" & str2 & "<br><br>Your current<b> " & .Fields(4) & " </b>for <b>" & .Fields(13) & "</b> will end on<b> " & .Fields(10) & " </b>.<br><br>" & str3 & " <br><br>Below are the Products/ Support/Services that are nearing expiry:<br>Service Details:<b> " & .Fields(4) & "<br></b>Expiry Date:<b> " & .Fields(10) & "</b><br><br><br>" & str4 & ".<br>Thanks for taking action now.<br><br> " & str5 & " <br><br><br><b><i>Thank You.<br><br>Customer Service<br>Company Name<br>support@domain.com<br><br>Customer Service<br><br><img src=""logo.jpg""></b></i></div></body></html>"""
End If

Open in new window



I want to insert a table like below in the same email as below. is there anyone who can guide me?
 --------------------------------------------------
|Service Details:        |& .Fields(4) |
 --------------------------------------------------
|Expiry Date:              |& .Fields(10) |
---------------------------------------------------
0
I am currently formatting a table in powerpoint in which I am tryin g to establish the following.


1. 1st ROW color fill RGB(0,39,77) , Text Color=white, Calibri -11 size

2. 1st column color RGB (0,39,77) , Text Color=white,Calibri - 11 size

3. 2ND ROW, 4RTH ROW, 6TH ROW ..... RGB(242,242,242) (LEAVING 1ST ROW)

4. 3th ROW, 5TH ROW, 7TH ROW ..... RGB(217,217,217)

5. ALL THE TEXT WILL BE CALIBRI RGB (89,89,89) SIZE = 11 EXCEPT 1ST ROW AND 1ST COLUMN IN THESE ROWS

6. INTERNAL BORDERS - WHITE , WEIGHT= 3, CONTINUOUS

7. AUTO FIT CONTENTS

PLEASE HELP ME TO WRITE THE FOLLOWING CODE AS I AM NOT ABLE TO ESTABLISH THAT IN ALTERNATE ROWS

THE SAMPLE IMAGE IS ATTACHED FOR REFERENCE.
0
VBA CODE TO REPLACE, POSITION AND REMOVE INDENTATION IN POWERPOINT TEXTPLACEHOLDER

The below code replace and positions my Text

 
Sub changeFont()
For Each aSlide In ActivePresentation.Slides
For Each aShape In aSlide.Shapes
If aShape.Type = msoTextBox Then
If aShape.TextFrame.HasText Then
If aShape.TextFrame.TextRange.Font.Name = "Franklin Gothic Demi" Then
If aShape.TextFrame.TextRange.Font.Size = 10 Then
      With aShape
            .TextFrame.TextRange.Font.Size = Replace(aShape.TextFrame.TextRange.Font.Size, 10, 25)
            .Top = 23
            .Left = 44
            .Height = 44
        End With

End If
End If
End If

End If
Next
Next
End Sub

Open in new window



However in addition to it I need to remove indentation in my text which should be

Before text = 0 cm, Special = None, Alignment = Left

Please help me to incorporate this in my code .

Thanks Team
0
Hello experts,
I have the following xml file (dummy file) in input folder.
I am looking for a vbscript & windows batch & powershell to generate the attached csv file in output folder.
How should I proceed?
If you have questions, please don’t hesitate to contact me.
Thank you in advance for your help.
export.xml
dummy-file-export.csv
0
CompTIA Security+
LVL 13
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Hello experts,
I have the following csv file (dummy file) in input folder.
I am looking for a vbscript & windows batch to generate the attached xml file in output folder.
How should I proceed?
If you have questions, please don’t hesitate to contact me.
Thank you in advance for your help.
dummy-file-export.xlsx
export.xml
0
I need to update the following macro. It is saving the sheet based on the value in B7. I need to add the current date to the file name when it saves. I tried to add a reference to z1 in the code, but I couldnt get it to work. I would like the date format to be 06-27-2019.

Sub Save_as_text_file()
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
    
    Dim fPath As String, fname As String
    Dim WS As Worksheet
    Dim data As String

    Set WS = ActiveWorkbook.Worksheets("CITY_TEMPLATE")
    fname = Application.ActiveWorkbook.Path & "\" & _
        ThisWorkbook.Worksheets("CITY_BUILDER").Range("b7") & _
        "_" & WS.Name & ".txt"
    fPath = Application.ActiveWorkbook.Path
    
    With WS
    .Visible = xlSheetVisible
    .Copy
    ActiveWorkbook.SaveAs fname, xlUnicodeText
    ActiveWorkbook.Close
    .Visible = xlSheetHidden
End With
    
    With CreateObject("Scripting.FileSystemObject")
        With .OpenTextFile(fname, ForReading, False, TristateUseDefault)
            data = .ReadAll
            .Close
        End With
        With .OpenTextFile(fname, ForWriting, True, TristateFalse)
            .Write Replace(data, """", "")
            .Close
        End With
    End With
End Sub

Open in new window

0
I am trying to run a VBS script to run a utilize command prompt to run notepad.  However I received Expected End of Statement.

Set WshShell = CreateObject("WScript.Shell")
WshShell.Run "command.com /k " & CommandLine start "" "C:\windows\notepad.exe"
Wscript.Quit

Open in new window

0
I utilize your code Active/Inactive  and Show All for my records and it  works great, I need to add Global error handling to the code and form...Please assist if possible, I'm still learning the VB side which I think is awesome for Access 2016....

Private Sub Active_Inactive_Click()
    If Me.Filter = "PL_Active=True" Then
        Me.Filter = "PL_Active=false"
        Me.FilterOn = True
    Else
        Me.Filter = "PL_Active=True"
        Me.FilterOn = True
    End If
End Sub

Open in new window

0
How can I pause for 3 seconds between these two lines of code?

DoCmd.OutputTo acOutputTable, "Data2", acFormatXLS, "C:\AmazonFeeds\Data2Export" & "\Data2-" & SuppID & ".xls", False


DoCmd.TransferText acExportDelim, "Data2SmallExportSpecification", "Data2", "C:\AmazonFeeds\production\outgoing\Flat.File.PriceInventory.us-" & SuppID & ".txt", True, ""
0
Hi

I have short powershell script that checks Symantec Endpoint Protection Antivirus Definition Dates and Production version. After executing this script I am getting output but the output is a little bit messy.
I would like to ask if anyone with PS experience can combine both line of scripts into one and generate clean output into CSV file and send via email.

Script:

Invoke-Command -ScriptBlock {Get-ItemProperty -Path "hklm:\SOFTWARE\Symantec\Symantec Endpoint Protection\CurrentVersion" -name productversion} -Computername $(Get-Content servers.txt)
Invoke-Command -ScriptBlock {Get-ItemProperty -Path "hklm:\SOFTWARE\Symantec\Symantec Endpoint Protection\CurrentVersion\public-opstate" -name LatestVirusDefsDate} -Computername $(Get-Content servers.txt)


The CSV file out put should contain only

PSComputerName      : FTC-WB(Server Name)
PSPath                           : Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Symantec\Symantec Endpoint Protection\CurrentVersion
PRODUCTVERSION      : 14.2.1031.0100
PSPath                           : Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Symantec\Symantec Endpoint Protection\CurrentVersion\public-opstate
LatestVirusDefsDate : 2019-06-17

PSComputerName      : lon-wg(Server Name)
PSPath                           : Microsoft.PowerShell.Core\Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Symantec\Symantec Endpoint Protection\CurrentVersion
PRODUCTVERSION      : 14.2.1031.0100
1
I need to migrate vb code to vb.net . the vb6 using DAO creates table in access data base using the database.CreateTableDef method.
is there any equivalent in ADO objects?
0
I have this code to send email based on the result of query in access . Email content will change based on vendor value.
It is working now. I would like to add an image based on the vendore value for example if the vendor is Microsoft I need microsoft logo in the Email if it is Cisco I need Cisco logo in the email.
Public Function Expiryafter45days()
Dim expirydate As String
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String
Dim email As Object
Dim MyStr1

 
Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("Expiryafter45days", dbOpenSnapshot)
 
With rsEmail
        .MoveFirst
        Do Until rsEmail.EOF
        
        
       
  
        
            If IsNull(.Fields(7)) = False Then
            
            Set email = CreateObject("CDO.Message")
            

email.Subject = "45 Day's Subscription/Contract Expiry Notification"
email.From = "GT Customer Service<noreply@domain.com>"
email.To = .Fields(9)
MyStr1 = StrComp(.Fields(11), "Cisco")
If MyStr1 = 0 Then
email.HTMLBody = "Dear " & .Fields(3) & " ,<br> Please be informed that your subscription/contract Cisco <b>" & .Fields(4) & "</b> will expire on <b>" & .Fields(10) & "</b>.<br>Please renew to avoid outage of service.<br> " & "<br><br><table><br></table><br><b><i><br>Thank You <img src ='cid:https://i.imgur.com/coF2veG.png' /><br><img src=/><br>Customer Service<br>Customer Service <br>support@domain.com<br><br>Customer Service

Open in new window

0
Hello experts,
I have a bunch of csv files located in C:\csv
I need to make sure that column I & M have the YYYYMMDD value. The rest of values will remain the same.
The idea is to have script  which reads the various files  in C:\csv, generates revised version of files as specified in previous point (YYYYMMDD value for column I & M)  in C:\out .
Columns involved by the change I & M should be related to a variable.
I attached dummy file.
If you have questions, please contact me.
Thank you in advance for your help.
0
CompTIA Cloud+
LVL 13
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

I need an Excel Macro to grab specific information in a sheet and export to a .csv with a specific name.

1. The Sheet name of the info is City_Builder
2. The City name will be in C8. This will need to be the name of the file. Example: Jacksonville.csv
3. The Data will be in F14:H18 that I need.

I need the macro to grab the information from the City_Builder F14:H18, and save that info into a .csv named after the contents in c8 in the same file location that the worksheet is in. For  example, if the City_Builder sheet is saved in the Desktop folder, I need the .csv file to be saved there as well.
City_Builder.xlsx
0
Hello experts,
I have the following modules attached which I used in my personal Add-in.
I have a mapping table to follow up the various sub procedures.
I would like to set up a script in order to.
Generate a csv by .bas in a yyyyymmdd_hhmm folder and report in column A the sub procedure name and in column B the name of .bas file name information should be reported in ascending order
I attached .bas files.
Example:
Sub procedure name 1;Bas name
Sub procedure name 2;Bas name
Sub procedure name 3;Bas name

If you have questions, please contact me.
g2bRangesFormulas.bas
g1aStringsFormulas.bas
0
I am trying to add a find and replace feature to look for an invoice number matching what is in range C7:C28 on sheet 1, with what is in column A on Sheet 4. If there is a match, I need it to copy the text adjacent to the invoice number on sheet one in range D7:D28 into column N on Sheet 4, on the same row the matching invoice number was found. So, if Invoice # 1234 was found on sheet 4 in A5, N5 should be changed to the text from sheet 1. I am new to this so hopefully my description is adequate. if not, I can answer any questions.
0
Hello experts,
I have the following procedure reported at:
https://www.experts-exchange.com/questions/29144099
Which allows me to transfer sub till end sub procedures into txt files based on a csv -mapping.
' Define needed I/O constants
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
Const TriStateUseDefault = -2

' Input file names
strWorkingDir = Replace(WScript.ScriptFullName, WScript.ScriptName, "")
cInFile = strWorkingDir & "\sub-procedures.txt"
cGroupFile = strWorkingDir & "\procedures-listing.csv"
cOutDir = strWorkingDir
cUnknownGroup = "Unknown-group"

' Create filesystm object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Resolve to absolute paths
strInFile = objFSO.GetAbsolutePathname(cInFile)
strGroupFile = objFSO.GetAbsolutePathname(cGroupFile)
strOutDir = cOutDir & "\" & Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2) & Right("0" & Hour(Now), 2) & Right("0" & Minute(Now), 2) & Right("0" & Second(Now), 2)

' Make sure input and match files exist
If Not objFSO.FileExists(strInFile) Then
    Wscript.Echo "*ERROR* Input file does not exist. (" & strInFile & ")"
    Wscript.Quit
End If
 
If Not objFSO.FileExists(strGroupFile) Then
    Wscript.Echo "*ERROR* Group file does not exist. (" & strGroupFile & ")"
    Wscript.Quit
End If

If Not objFSO.FolderExists(cOutDir) Then
    Wscript.Echo "*ERROR* Output folder does not exist. (" & cOutDir & ")"
    Wscript.Quit
End If

If Not objFSO.FolderExists(strOutDir) 

Open in new window

1
Can someone help me with a piece of vb? I have attached a dummy example below. I have data that I need to paste into a spreadsheet, the actual data will be literally thousands of rows (my example is just a few). I will be pasting the data into cell b2.  As the number of rows will always vary I need a solution which can extract the last row number and then using that copy the formula which is already in cell a2, from cell a3 to the last row of data. So to relate to the spreadsheet I have attached, the vb would return a row number of 9 and then copy the formula in cell a2 down to cell c9.
copydown-to-end.xlsx
0
Hi,

I have a problem on Word and macros.

I use this macro :
Sub refresh()

    Selection.WholeStory
    Selection.Fields.Update
    ActiveWindow.View.ShowFieldCodes = True
    ActiveWindow.View.ShowFieldCodes = False
    
End Sub

Open in new window


I want to run this macro with this .vbs:

Dim Word

Dim WordDoc

Set oArgs = WScript.Arguments

Set Word = CreateObject("Word.Application")

Word.Visible = False
Set WordDoc = Word.Documents.Open(oArgs(0))

Word.Run "refresh"

WordDoc.SaveAs2(oArgs(2))

WordDoc.Close(wdDoNotSaveChanges)

Word.Quit

WScript.Quit

Open in new window


here the command I launch to execute this vbs :

cscript C:\refresh.vbs C:\Users\admin\Desktop\a.docx refresh C:\Users\admin\Desktop\b.docx

And nothing happen... here

C:\refresh.vbs(14, 1) Microsoft Word: command failed

Do you have a solution?

Thanks
0

VB Script

37K

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.