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

How to calculate the sum between B = C+D+E using vb script  in excel sheet . B is already defined 0 . C - column , D - column and E - column have numeric value It should be affected each row of excel file.Please let me know if anyone know solution.
0
Hello,

The attached sample has a macro that runs a comparison between two worksheets based on ID and uses conditional formatting to note any non-matches.  I'm hoping someone can assist by editing the code so that it only identifies this mismatch if Reason 2 or Reason 3 appears in column G on File_A.  So on the attached example, once this change is made, row 6 would not be identified as a non-match because it shows Reason 1 in column G.

Thanks!
EE-Sample-Comparison-Only.xlsm
0
Background: Win XP, Access 2003 - due to legacy controls and client not ready to upgrade (go figure)

I have a VB script process which runs every 1/2 hour (from Windows Task Scheduler) to download data from an FTP site and write data to the Access database.  Lately, the client has experienced issues with some records not getting written to the Access database properly.  Yet I'm getting no indication that the ADO operation failed.

The code that writes the data to the Access database looks something like:

strSQL = INSERT INTO myTable (Field1, Field2, Field3) Values (1, 2, 3)
objConn.Execute strsql

where objConn has previously been defined.

I don't currently have any error handling in this process.  Is there a way for me to determine whether the Execute method failed (raised an error), without creating a recordset to check for the value of the key field in this insert process?
0
Using the following to create a backup of a root folder and copy everything from that to another location, however I would like a time stamp added so each time it runs, it creates a new instance of each folder and it contents.  It would be nice that it automatically deletes any folder that is >30 old.

oFSO.CopyFolder"\\[server]\HP-UFT\ScriptResourceFiles\FunctionalLibraries\*.*" , "\\[server]\HP-UFT\ScriptResourceFiles\ZZ\" , TRUE

Open in new window


Is this something easy?  -R-
0
Hi

I currently use the script below, which is scheduled daily and deletes txt files older than 30 days.

I want to add to this - I also want to delete files that the filename begins with cmr_ but am not sure how.

Any help appreciated

On Error Resume Next

Set oFileSys = WScript.CreateObject("Scripting.FileSystemObject")
sRoot = "\\fg-12\c$\Temp\root\Archived"			'Path root to look for files
today = Date
nMaxFileAge = 30					'Files older than this (in days) will be deleted

DeleteFiles(sRoot)

Function DeleteFiles(ByVal sFolder)

	Set oFolder = oFileSys.GetFolder(sFolder)
	Set aFiles = oFolder.Files
	Set aSubFolders = oFolder.SubFolders

	For Each file in aFiles
		dFileCreated = FormatDateTime(file.DateCreated, "2")
		If DateDiff("d", dFileCreated, today) > nMaxFileAge Then
			file.Delete(True)
		End If
	Next

	For Each folder in aSubFolders
		DeleteFiles(folder.Path)
	Next

End Function

Open in new window

0
Hello,

Please I want a script (vbscript or powershell) who can do the following steps :

Start Registry as an administrative user.
Navigate in computer regedit and find ID "HKEY_CLASSES_ROOT\AppID\{CDCBCFCA-3CDC-436f-A4E2-0E02075250C2}"
Take ownership of the node and assign full rights of the registry node.
Rename node RunAs to _RunAs.
Close Regedit.

Best regards,
0
Hi, Looking for a VBA solution where if two cells contents do not match, another cell's text is changed to red.  In the attached,  I want the text in column C to be red if the text in column D does not match the text in column E.
The result would be C14, C15, C17, C18, C19, C20 & C29 would all be red text

The example is only 43 rows but could be longer or shorter. It has to be VB. Cannot use conditional formatting in this case.Example.xlsm

Thanks in advance
swjtx99
0
Hello,

Please see attached for file structure.  I'm looking for a macro that will do the following starting on File_A worksheet row 2:

- IF File_A column C matches File_B column U then Compare File_A column H against File_B column Q.  If there is no match, enter YES in column O for that row.

- IF File_A column C matches File_B column U AND File_A column H matches File_B column Q then Compare File_A column G against File_B column O.  If there is no match, enter YES in column N for that row.

- IF File_A column C matches File_B  column U AND File_A column H matches File_B column Q AND File_A column G matches File_B column O then Compare File_A column K against File_B column R.  If there is no match, enter YES in column P for that row.

Please provide macro solution only as this is part of a more complex process.  Likely clear as mud but let me know if you have questions.  I'm using Excel Office 365 64-bit.

Thanks!
EE-Sample.xlsx
0
Hello,

Please review attached sample file.  I'd like a macro that does the following steps:
1 - Starts at row 2 on File_A, column C and looks for a match on File_B, column U.  If there is no match, conditional format the row (A2:M2) Red with White font.  Continues to the next row and repeats process until its passed the last row of data.

2 - Starts at row 2 on File_B, column U and looks for a match on File_A, column C.  If there is no match, conditional format the row (A2:W2) Red with White font.  Continues to the next row and repeats process until its passed the last row of data.

Note - I'm looking for a macro only as it's part of a more complex process.  Actual file could have 50,000+ rows of data in each worksheet.  MS Excel for Office 365 64-bit is the file version.

Thank you!
EE-Sample.xlsx
0
Hello,

Attached Excel file has 2 tabs showing the current format and the required format.  In the current format there is a row level start and end date.  I'm in need of a macro that will split this out so each date within that range appears as a separate row on a new tab (result).  For example, a row 2 with a start date of 07/12/2019 and end date of 07/15/2019 will duplicate that row 4 times and change the start date to 07/12/2019, 07/13/2019, 07/14/2019 and 07/15/2019, then delete the original row.

I then need the Days count in column J changed to 1 (as there is only 1 day now reflected in the row) and the Hours (column K) to match the Hrs/Day (column L).

Clear as mud but the sample file attached should provide some clarity.  I am looking for a VB macro only as this is a step in a more complex process.

Thanks!
EE-Sample.xlsx
0
Hi

I have a workbook that has data in and im trying to delete data that matches on sheet 1 & 2. When I mean match I mean an exact match in columns A-D

What I need it to do is look at data in sheet 1 & 2 and as stated if a complete match I need it deleted.  If missing from sheet 2 but in sheet 1 I need it to state "MISSING" in column E.

There will be occasions where data is duplicated in sheet 2. If this is the case and there is a match in sheet 1 I need it marked DUPLICATED but 1 entry deleting as its a exact match.

The enclosed workbook gives you some sort of idea what I mean.
Book1.xlsx
0
Hi.  There are times when DNS and a PC name do not match, and can determine this with PING -a, for example, I ping Computer 1 and returns an IP address, then PING -a the IP address and find the IP is associated with Computer2. I once wrote a simple batch that pinged the PC by name, captured the IP, then PING -a and if the PC names don't match, there's a conflict. That worked well.

Is there an elegant way to do this type of verification in VB.net?   I have an app that depends on connectivity to a remote PC host (on an AD domain), but it fails if there's a DNS conflict.  I'd like to verify there's an actual DNS issue in VB, instead of calling on CMD to check.
0
Hello,
I have a temp folder which contains multiple files.
I am looking for a procedure to launch every 1h to move from C:\temp to C:\Output the 50 newest files.
Example:
I have 700 files in C:\temp.
I execute the procedure and I move the 50 newest files.
One hour after I execute the procedure and I have the 50 newest.
The idea is to move the various files progressively.
If you have questions or another approach, please lest me know.
Thank you for your help.
1
Hi! I have run into a somwhat strange - to me at least - situation trying to kill Excel processes from my Windows 10 environment.

The case is this:

I ofte find that Excel leaves running, seemingly empty processes running in the background after I close an Excel workbook. I have a RPA-software (UFT) that uses VBScript and Excel to do its work, so I typically open up an Excel workbook and have the script getting its input data from there. My problem is that when VBS shall access the Excel workbook it typically picks one of the empty background processes, and not the workbook I opened.
I have tried everything I can think of to go throug the processes on the pc and kill the Excel processes, but to no avail. I can find them, no problem, but I cannot seem to be able to kill them off without manually open the Task manager and close them there, one by one until only the correct process, the one listed under Apps and not under Background processes is the only one left.

Can anyone please help me with this?

Thank you very much!

All the best from Iver in Oslo, Norway
0
I copied this code (UploadXML) from VB Forums and have used it in a MS Access 2003 application for over two + years now. BUT suddenly it has stopped working possibly due to a MS update? It now longer uploads the XML file.
Function UploadXML(strformurl As String, strFileName As String, inputfile As String, Optional strUserName As String, Optional strPassword As String) As String
    Const HTTPREQUEST_SETCREDENTIALS_FOR_SERVER = 0
    Const HTTPREQUEST_SETCREDENTIALS_FOR_PROXY = 1
    
    Dim WinHttpReq As WinHttp.WinHttpRequest
    Dim strBody As String
    Dim strFile As String
    Dim aPostBody() As Byte
    
    Dim bound As String
    Dim boundSeparator As String
    Dim boundFooter As String
    
    bound = "AaB03x"
    boundSeparator = "--" & bound & vbCrLf
    boundFooter = "--" & bound & "--" & vbCrLf
    
    Set WinHttpReq = New WinHttpRequest

    WinHttpReq.Open "POST", strformurl, False
    
    If strUserName <> "" And strPassword <> "" Then
        WinHttpReq.SetCredentials strUserName, strPassword, HTTPREQUEST_SETCREDENTIALS_FOR_SERVER
    End If
    
    WinHttpReq.setRequestHeader "Content-Type", "multipart/form-data; boundary=" & bound
    strBody = boundSeparator

    strBody = strBody & "Content-Disposition: form-data; name=""" & "login" & """" & vbCrLf & vbCrLf & strUserName
    strBody = strBody & vbCrLf & boundSeparator

    strBody = strBody & "Content-Disposition: form-data; name=""" & "pass" & """" & vbCrLf & vbCrLf & strPassword
    strBody

Open in new window

0
My Excel add-in that runs an external program object that doesn't return until another process interacts with it. That other process at this point is a VBScript (WScript.Shell Run) launched just prior to the external program object. Is there a way to integrate the logic of the VBScript into a subroutine of the Excel add-in such that I can invoke it in a separate thread. I know threading is not supported, so my solution would need to be implemented in a second Excel instance. Is this idea worth pursuing  or should I come up with a method to distribute a VBScript file as part of the add-in perhaps by creating it out of 200 rows of text constants?
0
VB .net creating mailitem via Microsoft.Office.Interop works for me but not others ??

They get the error...
System.BadImageFormatException: Retrieving the COM class factory for component with CLSID {0006F03A-0000-0000-C000-000000000046} failed due to the following error: 800700c1  is not a valid Win32 application. (Exception from HRESULT: 0x800700C1).

I've already tried Target x86 in the compile options with no luck.
I've tried 2 ways of adding the Reference....

1. Add Reference/COM
2. Add via Nuget PM > Install-Package Microsoft.Office.Interop.Outlook -Version 15.0.4797.1003 (with Embed Interop Types = True)

All variations work on my machine (64 bit Win10 Administrator accounts) but not others (also 64 bit Win10 Administrator accounts)

When adding reference via COM and i check the properties i have an Identity = {00062FFF-0000-0000-C000-000000000046}\9.6\0\primary so I'm also wondering if this is a clue as the error is reported on 0006F03A-0000-0000-C000-000000000046 ??

All help appreciated
0
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
Excel script runs on PC but when I run the script on MAC it is not working?
See coding below:


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRgSel As Range
    Dim xOutApp As Object
    Dim xMailItem As Object
    Dim xMailBody As String
    
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set xRg = Range("D2:D1511")
    Set xRgSel = Intersect(Target, xRg)
    ActiveWorkbook.Save
    If Not xRgSel Is Nothing Then
    
 '  Routine to send email
    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    Dim Flds As Variant

    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")

       iConf.Load -1    ' CDO Source Defaults
       Set Flds = iConf.Fields
       With Flds
           .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
           .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "my mail server.outlook.com"
           .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
           .Update
       End With
    rw = Target.Row

    strbody = "Status has been updated on the following RMA # " & Left$(Sheets("RMA2019").Range("A" & rw).Value, Len(Sheets("RMA2019").Range("A" & rw).Value) - 0)

    With iMsg
        Set .Configuration = iConf
        .To = "Somebody@me.com"
        .CC = ""
        .BCC = ""
        .From = """Lab Team"" <abcde@Somewhere.com>"
        .Subject

Open in new window

0
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
hi, i having issue on generating uptime details for windows machine. upon checking the the problematic server , these server do not has uptime.exe .

However when i open task manager as per attached screen shot. I can view the uptime details.

Please advice what command query can be use to grab this uptime details from a taskmanager output
taskmgr.PNG
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

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.