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

Due to a GPO I have to keep enabling TLS 1.0 and SSL 3.0.  I’m looking for a way to automate that process.
0
Hello,
this is a follow-up of a question posted to:
https://www.experts-exchange.com/questions/29066306/xlsx-xls-csv-conversion.html
here Bill Prew gave me an useful solution in this vbs script:

Const xlCSV = 6
Const xlTex = -4158
Const xlTextWindows = 20
Const msoEncodingUTF8 = 65001

strDir = "C:\Perl64\eg\alma"

Set objFSO = CreateObject("Scripting.FileSystemObject")

With CreateObject("Excel.Application")
    .Visible = False
    .DisplayAlerts = False

    For Each objFile In objFSO.GetFolder(strDir).Files
        strExt = objFSO.GetExtensionName(objFile.Path)
        If LCase(Left(strExt, 3)) = "xls" Then
            Wscript.Echo "Reading file: [" & objFile.Path & "]"
            With .Workbooks.Open(objFile.Path, False, True)
                With .Sheets(1)
                    .Rows(1).Delete
                    .Columns(1).Delete
                End With
                strPath = Replace(objFile.Path, "." & strExt, ".csv")
                Wscript.Echo "Writing file: [" & strPath & "]"
                .WebOptions.Encoding = msoEncodingUTF8
                .SaveAs strPath, xlTextWindows
                .Close False
            End With
        End If
    Next

    .Quit
End With

Set objFSO = Nothing

Open in new window

As the final csv is tab-delimited, I've discovered that the output of the script is wrapped with "" when there're commas in the content of some fields.
(say a content like a,B is rendered as "a,B")
As these commas are not important for me, is it possible integrating the script with a "find/relpace" that substitutes a space to the commas before all the other operations?
Thanks,
Fabiano
0
require to setup robo copy to copy without overwrite option . Copy files and folder. Copy files according to date . for an example copy files from 1st April 2012 to 31 june 2012.
1
Good Afternoon,

See attached excel template - I have basic spreadsheet that records equipment for PAT Testing
Template.xlsx

The last column of the spreadsheet has a review date..

I am looking for an automatic solution when the review date is due, I would like to send a email to a particular email address telling you that the equipment is due for testing.

In the email body..

I would like to include details of the equipment type from the relevant columns.

I am looking to the schedule the script to run on the server under task scheduler.

Thanks
0
Good Morning,

You have provided this code before - process to find a particular file and append the filename

Option Explicit

' Define files and folders to work with (NO TRAILING BACKSLASH)
Const cBaseDir = "D:\Audits\[[DATE]]"
Const cControlFile = "D:\WorkYardInspections\Output\WYIN_Data.txt"
Const cDelim = ","

' Text file I/O constants
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8

' Declare global variables
Dim objFSO, strBaseDir, strControlFile, strDate
Dim arrControl, strControl, arrFields, i
Dim strOldName, strNewName, strOldPath, strNewPath

' Create file system object
Set objFSO = WScript.CreateObject("Scripting.FileSystemObject")

' Get date in YYYYMMDD format
strDate = Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2)

' Resolve file paths
strBaseDir = objFSO.GetAbsolutePathname(Replace(cBaseDir, "[[DATE]]", strDate, 1, -1, vbTextCompare))
strControlFile = objFSO.GetAbsolutePathname(cControlFile)

' Make sure base folder exists
If Not objFSO.FolderExists(strBaseDir) Then
    Wscript.Echo Now & " ERROR: Base folder does not exist - " & strBaseDir
    Wscript.Quit
End If

' Make sure control file exists
If Not objFSO.FileExists(strControlFile) Then
    Wscript.Echo Now & " ERROR: List file does not exist - " & strControlFile
    Wscript.Quit
End If

' Load csv file into array
With objFSO.OpenTextFile(strControlFile, ForReading)
    arrControl = Split(.ReadAll, vbCrLf)
    .Close
End With

' Loop over each control record 

Open in new window

0
Hello,

I have basic email script and I have only included the section about attaching an attachment

With objMessage
    .To       = cToEmail
    .From     = cFromEmail
    .Subject  = cSubject
    .TextBody = "**** REMINDER - Due ****" 
    .AddAttachment "C:\Inbox\WYIN*.pdf"	
    .Send
End With

Open in new window


I understand using the .AddAttachment - you can only add a specific document using the exact file path.

I want to be able to use a wildcard in the attachment section - for example C:\Inbox\WYIN*.pdf"

Any ideas
1
Hi,
I am trying to use an  sql server 2008 SSIS  Foreach Loop Container to
run the same script against multiple SQL Servers.
felc I have one connection manager and I want to use this to connect to all the servers.
I have a variable set up so that the vb script task (ST current connection manager) can get a new servername on each iteration of the loop.
My problem is how to write the vb script so that it uses the same connection manager but changes the servername.
The code below looks for a Connection Manager with the variable name.
What I need is code to change the server name in the connection manager from the variable...
hope that makes sense.
any guidance appreciated.
Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
	' 
	' To open Help, press F1.

	Public Sub Main()
		'
        ' Add your code here
        Dim strConnect As String
        strConnect = "Data Source=" + Dts.Variables("User::ServerName").Value.ToString + ";Provider=SQLNCLI.10.1;Integrated Security=SSPI;Auto Translate=False;"
       'Dts.Connections("OLEDB").ConnectionString = strConnect

		'
		Dts.TaskResult = ScriptResults.Success
	End Sub

End Class

Open in new window

0
Greetings Experts!

Working with vbscript and a very limited knowledge of XML files, I'm trying to create a function that gets several pieces of information from the MaterialPosting element of an XML file.  

As long as I stick to elements that 1) are always present in the XML and 2) have values directly under the /JobInfo/MaterialPosting element, such as StockCode, I can get what I need but I'm having trouble retrieving the values of items that are one level deeper, such as Bins and Serials.  I was able to figure out how to get the value of the Bins/Bin element (see line 21 of the code below). However, it doesn't work for serials because the Serials element does not always exist.  

After spending the better part of a day with the Microsoft docs on XML Dom, my head is spinning and I still don't get it.  The code below is my latest attempt.  It returns an error of "object doesn't support this property or method" at line 11.  

How can I check to see if the element called Serials exists in my XML and, if the Serial element exists, retrieve its value?

Many thanks, in advance, for your help!



Dim objDom, objNodeList, Counter, myXML
   Set objDom = CreateObject("MSXML2.DOMDocument")
   objDom.LoadXML(XMLOut)

   Set objNodeList = objDom.SelectNodes ("//JobInfo/MaterialPosting")
       If objNodeList.length > 0 then

  ' Loop through each of the MaterialPosting elements
      For Counter = 0 To objNodeList.length - 1
          dim aSerialNode
          

Open in new window

0
Opening a vbs script from within Microsoft Access VBA - I get the error run-time error - Invalid procedure or call or argument.
with the yellow highlight on:  Shell "" & sOpenFileLocal & "", vbNormalFocus

Shell "" & sOpenFileLocal & "", vbNormalFocus
      DoEvents
      CurrentProject.Application.DoCmd.Quit
      Exit Function

Open in new window

eequestion.PNG
0
Hello experts,

I am having some space disk issues and I would like to monitor this without any installation.

My idea is to launch a command: powershell & windows batch or vbscript in order to:

1-Drill down root folder: C:\
2-List the various files sorted by sizes (highest to lowest) in a csv or txt file with specific locations

This will allows me to check the various files which takes the most and remove them to free up disk space.

If you have questions, please contact me.

Regards,
1
Looking for a powershell script that will allow me delete the profile path in profile tab for every AD user in a particular OU

Is it a case of using

Set-ADUser -Clear ProfilePath within OU
0
I am trying to read data into an Excel sheet with the code below but I get a type mismatch error. I get the data as confirm with the message box

    Set myrequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    myrequest.Open "Get", "https://api.iextrading.com/1.0/stock/aapl/chart/dynamic"
    myrequest.Send

    Dim Json As Object
    Set Json = JsonConverter.ParseJson(myrequest.ResponseText)
    
    'MsgBox (myrequest.ResponseText)

        i = Json(1)("marketClose") <---- Error
        ws.Range(Cells(n, 2), Cells(n, 2)) = i

Open in new window

0
I'm looking for a script or portable tool (ideally a Windows batch or VB as my office laptop don't have
PS access but Pwrshell scripts are welcome as I can do it on my personal PC with more efforts) that
could read a file containing a lists of SHA256 hashes (line by line is fine), input into
   https://www.virustotal.com/#/home/search  (the 3rd tab), hit ENTER, click on "Details" tab,
extract out the values under MD5 & SHA-1 & populate into 2 columns in a text file.

If the value can't be found in virustotal, return a "Nil" value for both columns.

I often get threat Intels that give IOCs' hashes in SHA256 but the trendmicro tool (EDR) tool I have can
only accept MD5 or SHA-1  hash values

Though I can enter them manually into virustotal,
0
Hi Experts,

We have a function that opens a CSV file, loops thru all records and uploads each record to a web site thru an API call.
Now we realized some time that site is offline and would like to add to the existing error routine below logic that if error number is 503 then it should wait a minute and try again call that API (the command that caused to throw the error).

Err_Proc:
    Select Case Err.Number
        Case 9
            '' display a message if you want
           Resume Next  '''' assuming you want to just pick up at the statement after the error
        Case Else
            s = "Insert into API_Errors(ErrorMessage,TableName, [TableID],FileName, ValuesSent) Values ('" & Err.Number & "--" & Err.Description & "','" & sTable & "'," & Nz(pk, "") & ",'" & sFile & "','') "
            CurrentDb.Execute s
'            MsgBox Err.Number & "--" & Err.Description, vbOKOnly
'            Resume ExitImportDataToCaspio
            Resume Next
    End Select

Open in new window


PS. for a detailed description of the function in question see here
.
Thanks
0
Can't Get this Macro to work.  Worked just ONCE.  Then, stopped.  What do you think is happening?

From this question: https://www.experts-exchange.com/questions/29076881/Word-Macro-for-Shift-F3-Like-Auto-Capitalization-That-Skips-the-Words-And-But-Or-For-and-To.html

Option Explicit

Sub SetTitlecase()
    Dim strLowerWords() As String
    Dim strProperWords() As String
    Dim rng As Range
    Dim rngWord As Range
    Dim i As Integer
    
    'set up except words
    strLowerWords() = Split("and,for,to,a,of", ",")
    
    ReDim strProperWords(UBound(strLowerWords))
    For i = 0 To UBound(strLowerWords)
        strProperWords(i) = UCase(Left(strLowerWords(i), 1)) & LCase(Mid(strLowerWords(i), 2))
    Next i
    
    'set range to work on
    Set rng = Selection.Range
    
    With rng
        'capitalise first letter of every word
        .Case = wdTitleWord
        
        'set first letter of minor words to lower case
        For i = 0 To UBound(strLowerWords)
            With rng.Find
                .Text = strProperWords(i)
                .Replacement.Text = strLowerWords(i)
                .MatchWholeWord = True
                .MatchCase = True
                .Execute Replace:=wdReplaceAll
            End With
        Next i
    End With
    
    'recapitalise first letter of sentences
    rng.Case = wdTitleSentence
End Sub

Open in new window

1
Hello Experts,

I have about separate 10 web pages for users. Instead of sending them the URL each time a new person needs access to the page, I’d like to set it up as so:

I have an app that will open each of the web pages when you select it. I’d like some way to authenticate the user so it’ll only go to the page for them.

So let’s say Tom needs access to FINANCE and GRANTS pages, but should not see the rest. I would want to add Tom to the FINACE and GRANTS group so when he opens the app below, his only options would be HRD.html and Finance.html pages.

I suppose I could do some case statements in the script and a prompt to validate hard coded user names but I’d rather not put user names in the script.

Could I use a prompt to authenticate to a file/group before opening the page?  Any ideas? Thanks!

<html>
<head>
<title>Apps</title>
<HTA:APPLICATION
   Application ID = "Test"
   APPLICATIONNAME = "Test"
   BORDER="thin"
     SCROLL="no"
     SINGLEINSTANCE="yes"
     WINDOWSTATE="normal"
	 SysMenu="no"
 />
</head>
<script language="vbScript">

Sub Window_onLoad
window.resizeTo 680,470
End Sub

Sub GetURL
thisURL = urldrop.value
document.getElementById("noiwin").innerHTML = "<iframe width='800' height='500' src='" & thisURL & "'></iframe>"
End Sub

</SCRIPT>
<body>
<select name="urldrop" style="width: 200px" onchange="GetURL">
<option value="AppSelect.html">Select Application</option>

<option value="Finance.html">FINANCE</option> - If username in 

Open in new window

0
I have a ASP.NET website that we use for our client portal.  It is written in VB.  I created a DirectoryInfo link that works great and gives the users the ability to see a directory listing and download the file.  I have to make sure that the files in the directory do not have spaces or the link gives an error.  Is there any way to change the code to retrieve the file that has a space in it  Below is an example of my code.  Any help would be appreciated.

  Protected Sub Label11_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Label9.Load

        Dim strFileSize As String = ""
        Dim di As New IO.DirectoryInfo("e:\printed statements\" & Label7.Text & "familyoffice\" & "2019\" & "february\" & "monthly_reports\")
        Dim aryFi As IO.FileInfo() = di.GetFiles
        Dim fi As IO.FileInfo

        For Each fi In aryFi
            strFileSize = (Math.Round(fi.Length / 1024)).ToString()
            Label11.Text += "<a href=https://www.brandytrust.net\Clientfiles\" & Label7.Text & "familyoffice\" & "2019\" & "february\" & "monthly_reports\" + fi.Name + ">" + fi.Name + "</a><br /> "

        Next

Open in new window

0
Hi Experts,

I had the following code working perfectly fine on my pc.

   sDir = "E:\AppDev\FTP\Caspio"

    StrFile = Dir(sDir & "*" & sFileStr & "*")
    Do While Len(StrFile) > 0 And Not IsFileOpen(sDir & StrFile)

Open in new window


However when placing it into the server it always returning an empty string.

Folder above contains many files (on that server).
0
Good Afternoon,

I have this VBS script for sending emails but I want to add a section to the script where it looks for a particular file and adds it as attachment.

' Constants
Const cSmtpUser = "Username"                                ' *** MAKE CHANGES HERE ***
Const cSmtpPassword = "Password"                            ' *** MAKE CHANGES HERE ***
Const cSmtpServer = "smtp.test.net"                          ' *** MAKE CHANGES HERE ***
Const cSmtpPort = 25                                       ' *** MAKE CHANGES HERE *** (25, 465, 587 common)
Const cFromEmail = "admin@test.uk"                      ' *** MAKE CHANGES HERE ***
Const cToEmail = "nick@test.uk"                        ' *** MAKE CHANGES HERE ***
Const cSubject = "Daily Worksheet - Operatives Missing"                     ' *** MAKE CHANGES HERE ***

' CDO Constants needed to send email
Const cCdoSendUsingPickup = 1   'Send message using the local SMTP service pickup directory.
Const cCdoSendUsingPort = 2     'Send the message using the network (SMTP over the network).
Const cCdoAnonymous = 0         'Do not authenticate
Const cCdoBasic = 1             'basic (clear-text) authentication
Const cCdoNTLM = 2              'NTLM
Const cCdoSendUsingMethod        = "http://schemas.microsoft.com/cdo/configuration/sendusing"
Const cCdoSMTPServer             = "http://schemas.microsoft.com/cdo/configuration/smtpserver"
Const cCdoSMTPServerPort         = 

Open in new window

0
Good Afternoon,

PPE_Data.txt

I am looking for a straight forward script that can re-sort the attached text file.

Source File

"Date:","PPE No."
"07 February 2019 07:06 AM","PPE_000018_1067414"

I would like to move PPE No to the 1st the column and it looks like the below

"PPE No.","Date:"
"PPE_000018_1067414","07 February 2019 07:06 AM"

Thank you
0
Dear All

I have a spread sheet which I am trying to remove the data from.  I have a large spread sheet that I wish to use as a mail merge source so I can contact everyone and tell them I don't have their contact details.
So I need to delete all the rows that have an email address in it.  

Please help

Thanks

Simon
0
Hi Experts,
I have some (Access) code that is supposed to be running all the time.
Would like to get an email notification in case the code stops executing for whatever reason.
What is the best way to accomplish that?
Thanks
0
Hi Experts,

I have the following code running that suppose to process all files in a folder, and deletes each of those files upon completion.

Public Sub CallImportDataToCaspio()
   Dim StrFile As String, strTable As String, sFileStr As String
   Dim sDir As String
   Dim l As Long, s As String, i As Long
   Dim db As Database
   Set db = CurrentDb
   sDir = "H:\FTP\test\"
    'StrFile = Dir(sDir & "*PatChanges*")
    StrFile = Dir(sDir & "*" & sFileStr & "*")
    Do While Len(StrFile) > 0 And Not IsFileOpen(sDir & StrFile)
''        Do While IsFileOpen(sDir & StrFile)
''            ' do nothing
''        Loop
        If InStr(1, StrFile, "Full") = 0 And InStr(1, StrFile, "Part") = 0 Then
            i = CountOfRecords(sDir, StrFile)
            If i > 1 Then
                'Debug.Print StrFile & " - " & CountOfRecords(sDir, StrFile)
                If InStr(1, StrFile, "PatChanges") > 0 Then
                    strTable = "Patients"
                    l = ImportDataToCaspio(strTable, sDir & StrFile, i)
                ElseIf InStr(1, StrFile, "SchChanges") Then
                    strTable = "Schedule"
                    l = ImportDataToCaspio(strTable, sDir & StrFile, i)
                ElseIf InStr(1, StrFile, "CGChanges") Then
                    strTable = "Caregivers"
                    l = ImportDataToCaspio(strTable, sDir & StrFile, i)
                ElseIf InStr(1, StrFile, "PatMedProfileChanges") Then
                    strTable = 

Open in new window

0
I am setting up  Google Mapping of our properties (nearly 1000 addresses) for my office and need to do a lot of filtering of Excel data. The multiple clicks to filter is driving me crazy. I am looking for a way to get the result of the Filter on the Address column the same way as the built-in one but with an input box. So far my VBA Macro search has netted more problems than I have time for!

I want to be able to type in either the Number or the street name and get a filter of all the fuzzy matching results. And hooking it to a macro to automate it. I have run the Record Macro so many times my fingers hurt!

Sample Data file attached. Not worried about the other columns as long as they stay in the array.

Any help?  Thanks!
RB-Example.xlsx
0
Hey All,

How can I get the output from the following commands please.

562       Set wsh = New WshShell
         
572       strCom = Chr(34) & _
                 "C:\Apps\PSCodeRunner\RunCode.cmd" & _
                 Chr(34)
582       lngErrorCode = wsh.Run(strCom, _
                           WindowStyle:=0, _
                           WaitOnReturn:=True)

I have a powershell being executed in that file as follows:

C:\windows\sysnative\WindowsPowerShell\v1.0\powershell.exe -NoExit -Command "cd \; [console]::Title='RC PS Processor'; [IntPtr]::Size; c:\apps\PSCodeRunner\PSScript.ps1; Exit"


I have tried piping in clip such as | clip but the only kind of output I get is:

WARNING: The names of some imported commands from the module 'tmp_hsh4edta.1jr' include unapproved verbs that might
make them less discoverable. To find the commands with unapproved verbs, run the Import-Module command again with the
Verbose parameter. For a list of approved verbs, type Get-Verb.

ModuleType Version    Name                                ExportedCommands                                            
---------- -------    ----                                ----------------                                            
Script     1.0        tmp_hsh4edta.1jr                    {Add-AvailabilityAddressSpace, Add-DistributionGroupMember...

And what I want is the actual output you get from the PS ISE etc... or PS generally.

Please advise

Many thanks

R
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.