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

We have a Client record.  This has various fields as expected, including ClientID
We also have Invoice records.  These are associated with a Client via ClientID.
The Invoice records also have fields – Paid (yes/no) and Waived (yes/no)

On the Edit a Client form I want to have a button that will “Cancel” the Client.  This means make the client inactive, but I want to ensure that there are no unpaid invoices remaining.
So the code needs to do….

•      Search for an associated Invoice that is unpaid (and not waived)
•      If none exit..
•      Offer the user an option to set as Waived (another yes/no invoice field)
•      Set the invoice as waived
•      Search for a second qualifying invoice
•      If none exit..
•      Set Client to Inactive.

I have done a small amount of VB in the past but I cannot figure out how to go about doing the above and need a few clues please.

Regards
Richard
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.

Hi Experts,

We have a system that exports time-sheets to CSV file, and from there it goes further...
There is also another column that includes the total hours.

Since users enter some times the timing with AM/PM and sometimes not.
We would like to have a function that will do the following.
Open that CSV file, update the time fields with AM/PM according to the total hours.

Would prefer either a VB Script or a VBA function.

PS. The Total hours column can be off with an hour or so (which is the lunch break).

Attaching example.

Thanks in advance.
Book1.csv
0
I have columns that have formulas which are not part of the userform.  When I add a new record only the fields from the user form are added to the new row.  However, I have columns with formulas that need to be copied down once a new record is added.

Currently the columns that need to be copied down are col O and then T thru AB.  However, I would like any new added information that is not part of the form to be added automatically.

In the attached file I highlighted in yellow  what column rows need to be added each time a new record is added. In this attachment there are no formulas but in the original these columns are formulated.
29136966e-v1.07ab.xlsm
0
I have a vbscript that tests the status of a service every 30 minutes.  

When it executes, it writes a timestamp to a log file and a little message.

I've noticed that the midnight timestamp only writes the date and not the time.   See it below.  Why is this?

Here's the command:

objTextFile.WriteLine (now)

Here's the output:

3/7/2019 11:30:00 PM
Starting Webpage Status Check
Webpage is available.
------------------------------------------------------------------------------------
3/8/2019                                                    <-- notice only the date is written.
Starting Webpage Status Check
Webpage is available.
------------------------------------------------------------------------------------
3/8/2019 12:30:00 AM
Starting Webpage Status Check
Webpage is available.
------------------------------------------------------------------------------------
0
Hello,
the question is related to the script accepted in:
https://www.experts-exchange.com/questions/29137575/getting-rid-of-commas-in-a-vbs-script.html
only for certain excel files I obtain the error message enclosed.
the translation is more or less: "error with the method SaveAs for the Workbook class"
Thanks
Fabiano
error.jpg
0
I am trying to understand part of the format of the following Query

Set colItems = objWMIService.ExecQuery("Select * from Win32_OperatingSystem",,48)

I've been unable to find out what the 48 means, can you help
Also, where do I look to see the format of the Query, like what belongs between the two commas -  ,,48
0
Good Morning,

I am trying to tidy up some scripts and merge them into one..

The process is as follows..

  1. PDF is automatically downloaded to a folder - The folder name is based on the current date - Format YYYYMMDD - The PDF document is named as follows PPE_000035_1067414.pdf.  PPE_??????_???????.pdf the letters are always standard but the numbers always change.

  1. The next process is that a CSV file is automatically downloaded named as follows 'template_c9b825a390544c7b98b57022e0593e44.csv' stored in a folder. Within this file I need extract the PDF ID and the date. There can be multiple entries. See example file
  2. template_c9b825a390544c7b98b57022e05.csv
  3. From this CSV file I want to extract  rows where datetime      Date: and textsingle      PPE No. - are shown columns A & B then extract the corresponding data in column C but each of set of data starts from section Title Page. The data extracted in the example should be as follows and saved in a file
  4. PPE_000036_1067414, 05/03/2019 07:35
  5. PPE_000037_1067414, 05/03/2019 08:40
  6. The next process is to rename the PDF file that was downloaded earlier in the YYYYMMDD folder.
  7. I would like the script to search for the PDF folder document in the YYYYMMDD folder based on the data that was extracted from the CSV file, 1st Column name of PDF File and once a match has been found append the data at the end of the filename. Removing any illegal characters etc and adding underscore as the separators.
  8. PPE_000036_106714_05_03_2019_07_35_AM.pdf
1
Hi I'm tidying up workbooks and come across this macro and not got a clue what it does. Possibly think that it updates headers on a workbook that I have but don't know where or what it is.    Can anyone advise me on what it does?

Thanks

Sub update()
'
' update Macro
' Macro recorded 08/05/2006 by Silverlink Train Services
'

'
    Workbooks.Open Filename:="S"
   
    :\SHARED\passacc\Excel\passacc\SUMMARY.xls", _
        UpdateLinks:=3
    Windows("CONTROL.XLS").Activate
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 31
    ActiveWindow.ScrollColumn = 30
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 28
    ActiveWindow.ScrollColumn = 27
    ActiveWindow.ScrollColumn = 26
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
  …
0
I have a large text file that consists of the top 11 lines of a bunch of dns zone files.  I need to parse this file and create a new file that generates a single line for every iteration of the pattern.  Essentially the pattern consists of 11 lines, followed by a domain line and then repeats.  For example, the pattern looks like the following:

; *** This file is automatically generated by Plesk ***
$TTL	86400

@	IN	SOA	dauth5.domain.com. contact.domain2.com. (
			2018020603	; Serial
			10800	; Refresh
			3600	; Retry
			604800	; Expire
			10800 )	; Minimum

---------------domain2.com

Open in new window


This pattern repeats over and over for every domain name contained on our server.  What I would like is to parse the file for each of these 11 lines and return the following:

domain2.      SOA    dauth5.domain.com.  2018020603 10800 3600 604800 10800

The "domain2" above comes from line 11 each time.  Can anyone write a script or perhaps a quick way in Notepad++ to get the data formatted the way I want?  Thanks!
1
I have used this VB technique on many occasions to dump the contents of a text box into a Word document, but this time it just won't work.  The macro fails at  Selection.TypeText Text:=DescriptionTextBox.Text.  Please see attached Next_Item Macro.  Is there a better way to do this?  Any examples are appreciated.

Sub Next_Item()
'
' Next_Item Macro
'
    Selection.GoTo What:=wdGoToBookmark, Name:="NextItem"
    Selection.Find.ClearFormatting
    With Selection.Find
        .Text = ""
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.TypeText Text:=DescriptionTextBox.Text
    Selection.TypeText Text:=vbTab
    Selection.TypeText Text:=ValueTextBox.Text
    Selection.TypeParagraph
    Selection.GoTo What:=wdGoToBookmark, Name:="NextItem"
    Selection.Find.ClearFormatting
    With Selection.Find
        .Text = ""
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    ActiveDocument.Bookmarks("NextItem").Delete
    With ActiveDocument.Bookmarks
        .DefaultSorting = …
0
OWASP: Threats Fundamentals
LVL 13
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

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
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
Exploring SQL Server 2016: Fundamentals
LVL 13
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

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

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.