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

Excel: Create new Sheet, parse heading into column against content of that column.

Example excel worksheet contains Sheet 1 with Columns A B C as address columns and intermittent rows containing contents under different headings in about 50 more columns.
Objective:
Copy content to Sheet2 as per: For each column from Column D to Column BB, where a row contains content, copy the address row for Columns A B and C, plus the content from the row for the column, and add the column heading into the adjacent cell on the same row as per the example in Sheet 2.

EEExample.xlsx
0
PowerShell drag and drop, then hash the file that was dropped onto the form.
I can't figure out where to add something like "$fileHash = Get-FileHash -path $file -A SHA256" and then append to the file path.
The code below shows the file path when dragging a file, I want to hash that same file and add it's hash to the end
c:\some\file\path\here.txt, F70363B8AE41B113AC96F9762DB46262B8F436B29A4B7D1E2A3ADAB101E75299
I may further call a VirusTotal API call to look the hash up and append more info on the end later.
Right now all I need is the hash of the file(s) that get drug onto the form.

[void][System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
$Form1 = New-Object System.Windows.Forms.Form
$Form1.ClientSize = "391, 190"
$Form1.TopMost = $true
$TextBox1 = New-Object System.Windows.Forms.TextBox
$TextBox1.Anchor = "Top,Bottom,Left,Right"
$TextBox1.Location = "12, 12"
$TextBox1.Multiline = $true
$TextBox1.ScrollBars = "Both"
$TextBox1.Size = "368, 166"
$TextBox1.AllowDrop = $true
$TextBox1.add_DragEnter({FNprocess($_)})
$Form1.Controls.Add($TextBox1)
function FNprocess( $object ){
  foreach ($file in $object.Data.GetFileDropList()){
    $TextBox1.AppendText($file+[char]13+[char]10)
  }
}
[System.Windows.Forms.Application]::Run($Form1)

Open in new window

1
Hello experts,

I have the following procedure:
' File I/O constants
Const ForAppending = 8

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

' Define folders and files to work with
strScriptDir = objFSO.GetParentFolderName(objFSO.GetFile(Wscript.ScriptFullName))
strLogFile = strScriptDir & "\log-copy.txt"
strStamp = TimeStamp(Now)
arrFolders = Array(ScriptDir)
strFromFolder = ""          ' Do not add trailing "\" to this variable
strToFolder = strScriptDir  "\Backups" ' Do not add trailing "\" to this variable
blnFolders = True
arrSkipFolders = Array("")

' Open log file for appending
Set objLog = objFSO.OpenTextFile(strLogFile, ForAppending, True)

' If the csv backup folder does not exist create it
strToPath = strToFolder
If Not objFSO.FolderExists(strToPath) Then
   objLog.WriteLine Now & " INFO: Created folder """ & strToPath & """."
   objFSO.CreateFolder(strToPath)
End If

' Create a folder for todays backups
strToPath = strToPath & "\" & strStamp
If Not objFSO.FolderExists(strToPath) Then
   objLog.WriteLine Now & " INFO: Created folder """ & strToPath & """."
   objFSO.CreateFolder(strToPath)
End If

' Loop through all folders, backup files
For Each strFolder in arrFolders
   BackupFilesFolders strFolder, blnFolders
Next

' Wrap up
objLog.Close

Wscript.Echo Now & " :" & vbCrLf & "Program has completed all the actions successfully"


' Process folder
Sub BackupFilesFolders(strPath, blnFolders)

   ' If the input 

Open in new window

0
Greeting Experts

I need to create a script to get a list of users in the Local Administrators Group on remote work stations. The script just needs to remote into each workstation using a text file with a list of device names and gather the names & Groups who are in the local Administrators Group in each pc.... Can somebody help me with this issue...
1
Excel ID rows in Column A are intermittently repeated.  The request is for a script to sum the repeated values in Column B and copy ID and total to Column D and E as per manual example in attached file.

EE-problem-file---ID---Count.xlsx
0
Greetings Experts!

Working with vbscript and a limited (but growing, thanks to help received here!) knowledge of XML files, I'm trying to create a function that totals the quantities of materials issued to jobs by lot, bin, and serial number.  My XML file contains both material allocations (i.e. materials needed but not yet used) and material postings (i.e. specific information about materials that have been used).

In words, here’s what I’m trying to do:

For each material allocation, check to see whether the allocation has been completed. If it has not been completed, extract some information about the allocation from the XML and store it in a variable.  If it has been completed, look in the material postings and total up the quantities posted by lot, bin location, and serial number then store only those postings where the total quantity is not zero in the variable.

My latest attempt at this involved loading the material postings elements into an array and looping through that looking for lots that match the current lot and adding up the quantities. It kinda works but this will certainly take too long when my BOM has 500 items on it instead of 5 items.  Code shown here for context.


' create array of material postings
Dim arrMaterialPostings
arrMaterialPostings = BuildMaterialPostings(StockCodeSelection.CodeObject.Job)


Dim objDom, objAllocationsList, Counter, MyXML, objSerialNode, objBinNode, strBin, strSerial, EmptyXML
Dim objNodeList2, Counter2, 

Open in new window

0
I want to copy the application installation file or a vbs script on a share created on a file server.

on my workstation I want to have a text file with computer names in txt or csv format.

want to use a tool like psexec that executes a command on the remote computer

run the application or the vbs file on all the computers listed in the text file.

clients are windows 7 sp1

appreciate help
0
Hi Experts,

I have a report exported to Excel that contains info with headers and details.

looking to write a script that will just obtain all details along with the name portion of the header.

Purpose of this, to export all this into a SQL table.

See example attached.
Untitled.png
0
Dear Experts,

I have the target to open all the existing .xls so Excel files in a certain folder through VB Script.

Actually found the following code which looks promising straightly providing that:

Set objFolder = objFSO.GetFolder("D:\Test")
For Each objFile in objFolder.Files
   If LCase(Right(objFile.Path, 4)) = ".xls" Then
      'do something
   End If
Next

Open in new window


although pasting that code to a file with .vbs extension and double clicking on it, it brings VBScript runtime error:

Object required: 'objFSO'

Could you please advise how to fix that, eventually having some other code which could provide the same target?

Thanks in advance,
0
I need to run maco in Excel using command line or VBS. Please find attached the excel file with the maco and the vbs I created but it was not working.

Regards,

Dallag

'Create an instance of Excel
Set xlsApp = CreateObject("Excel.Application")
'Remove the following line to open Excel in the background
xlsApp.Visible = true
'Open your file
Set xlsWB = xlsApp.Workbooks.Open("D:\0 Aramco\0 Prpjects\0 UR\NA\System-A-B1-new-format_test_v3.xlsm")
'You can call macros connected to sheets or the workbook
Set xlsSheet = xlsWB.Worksheets(1)

'Call xlsWB.MakeFinal() 'To call a macro in ThisWorkbook
'Call xlsSheet.macroname() 'To call a macro in Sheet1

'False here just makes it not prompt to save
xlsWB.Close(False)
xlsApp.Quit

Open in new window

new-format_test_v3.xlsm
0
VBA Syntax

I have this line: Format(Date, "YYWW") but in January (e.g. 31/01/2019) this returns '195' week 5 is a single digit.

For single number weeks I need it to be in 2 digits so wee 5 would be '05'

Anyone know how?
0
Dealing with Locked Tables during debugging

I am tracking down an obscure bug which MAY be in the VB code or MAY be in any number of stored procedures and PROBABLYinvolves the delicate inter-dependency each has on the other.

So, while I am stepping through the VB code, I query the database and see the values before and after each step. But, just when I REALLY need to run the query, the table is locked (by the executing code). After the table is closed and the transactions are committed, only then can I run the query, but by then, it's too late. The error reveals itself and I had no way to see what was on the  database.

Suggestions?

It would be awesome if I could set up a mirrored database that I could query to my heart's content. Is that an extreme idea?

I think I am going to need just such an extreme idea to do this...

Does SQL Server 2017 provide me anything I could do here? Ae there any kind of log files where these transactions can be viewed, even when the table is locked?

Maybe Visual Studio is the problem and I could benefit from getting a log of all data tables changes without even stepping through the code. Is there an SSIS feature or SQL Server feature that could output all DB changes for each table into a text file, for example? Then, I could scan it and see when the error showed up in the data.

Thanks
0
Hello,

I'm looking for a macro that will find the last row of data and then drag a formula from a specific cell down to that last row.  In the attached example, the formula is in C2.  I'd like the macro to identify row 6 as the last row containing data and then drag the formula from C2:C6.  The end result would show cells C3 and C4 as 'IV'.

Thanks,
0
Environment:
Win10

What I'm trying to do
I'm trying to add to an existing VB Script (not VBA)  running locally, the ability to find and 'click' a hyperlink on a webpage.

One reference to such a script is:

https://www.experts-exchange.com/questions/21352099/VBscript-to-click-a-link.html

In the answer, Erick states:

'To do this you need to add references to
"Microsoft Internet Controls"
"Microsoft HTML Object Library"'

Question
I know how to do this in a VBA Project but can't find where to add such a library reference to a VB Script?

Is there a way to add this reference so it is available to all of my native VB Scripts (ie vb scripts I run from my own pc)  without having to create the reference to those libraries in each VB Script I develop?

And the opposite … what do I need to add to a local VB Script (assuming it was able to reference such libraries through my local global settings) if I wanted to send the script to another user who didn't have those library references installed on their system?

Available Tools
I normally develop my VB Scripts using notepad++.  I've just now downloaded Visual Studio 2019 RC (free edition) thinking I might be able to use it to add a reference library to this VB script but can't find how/where that would be done in VS (I'm a total novice with VS so it may be staring at me and I'm not recognizing it).
0
Hi Experts,

I have a function that does as follows.

Opens a given CSV file, reads in a loop each line of the file and uploads data to a web site.

I have an error procedure that whenever it returns in error (usually from that web site) it keeps a log of that error and moves on to process the next line.

Error procedure below.
ExitImportDataToCaspio:
    Close #1
    Set db = Nothing

    Exit Function
Err_Proc:
    s = "Insert into API_Errors(ErrorMessage,TableName, [TableID],FileName, ValuesSent) Values ('" & Err.Number & "--" & Err.Description & "','" & sTable & "'," & Nz(pk, "") & ",'" & sFile & "','') "
    CurrentDb.Execute s
    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
'            MsgBox Err.Number & "--" & Err.Description, vbOKOnly
'            Resume ExitImportDataToCaspio
            Resume Next
    End Select
End Function

Open in new window


Now would like to modify that as follows.

Have a table store some errors, in case this error is one of those, it should (instead of processing the next line) try one more time to process the current line.

For detailed description of the function see this..

Thanks
0
Can someone help me I'm using a Zenworks command called zac ref from a commandline Zenworks allows you to run a remote script I just don't know how to incorparate the zac ref here is simple vb script
strComputer = "webserver"
Set objWMIService = GetObject _
    ("winmgmts:\\" & strComputer & "\root\cimv2:Win32_Process")
 
Error = objWMIService.Create("notepad.exe", null, null, intProcessID)
If Error = 0 Then
    Wscript.Echo "Notepad was started with a process ID of " _
         & intProcessID & "."
Else
    Wscript.Echo "Notepad could not be started due to error " & _
        Error & "."
End If

Open in new window

0
Hi,

I am looking for a script that will tell me which AD  users have changed there passwords in the last 24 hours.

Was wondering if anyone had something similar.
0
Want to create an Excel macro that will take someone to the desired sheet by using the contents copied into the clipboard, but I have two problems:
1. When I run the macro below, I get a "Compile Error:  User-defined type not defined" on the first line.  The help reference didn't help (https://docs.microsoft.com/en-us/office/vba/Language/How-to/user-defined-type-not-defined).
2. When I tried to save the macro, it said that in order to save it with a VB Project, I needed to "choose a macro-enabled file type in the File Type list, but I couldn't find the File Type list under any of the menus and couldn't find it in Excel Help.  Thanks.

Dim DataObj As New MSForms.DataObject
Dim myString
DataObj.GetFromClipboard
myString = DataObj.GetText
   
Sheets(myString).Select
    Range("A1").Select
0
Hi Experts,

This is in reference to the following.
I would like to have the lunch column getting populated with the correct amount.

Thanks in advance.
0
I have this macro that I believe compares the data in file called "compare" with data in another folder of my choice of which I put the name in  the file called compare.

Its then meant to look within the folder for the file name and compare that with what I have in the file called "compare"

I tried running it and it says that that it cannot find the file that im comparing  because its on the desktop of my PC.   I don't have any files on the desktop.

All files are in a folder called compare of which has the compare macro file and all other files that it has to look in.

Any ideas?

Private Sub CommandButton1_Click()
Dim WBs As Workbook, WBc As Workbook, cpath As String
Dim WSs As Worksheet, WSc As Object
Dim i As Long, j As Long, k As Integer, C As Boolean, R As Integer
Set WBs = Application.ActiveWorkbook
cpath = Application.ThisWorkbook.Path
Set WSs = WBs.ActiveSheet
Set WBc = Workbooks.Open(cpath & "\" & WSs.Cells(3, 8))
Set WSc = WBc.Sheets(WSs.Cells(3, 9).Value)
i = 4

C = False
Do While WSs.Cells(i, 1) <> ""
    j = 2
    C = False
    Do While WSc.Cells(j, 1) <> "" And C = False
        R = 0
        k = 1
        Do While WSs.Cells(i, k).Value = WSc.Cells(j, k).Value And k < 5
            DoEvents
            k = k + 1
        Loop
        If k = 5 Then
            C = True
            Exit Do
        End If
        j = j + 1
    Loop
    If C = False Then
        WSs.Range("A" & i, "D" & i).Interior.ColorIndex = 6
    Else
        WSs.Cells(i, 5) =

Open in new window

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

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.