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

I need to add a fourth key to this simple sorting macro but it bugs when I add a fourth key. Is there a way around that?

Sub SortSelection()
Selection.Sort key1:=[A3], Order1:=xlAscending, _
               key2:=[B3], Order2:=xlAscending, _
               key3:=[C3], Order3:=xlAscending ', _
               'key4:=[L3], Order4:=xlAscending
End Sub

Open in new window

0
C++ 11 Fundamentals
LVL 13
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

I am trying to create an xml file via vbscript.  I can create the file but it inserts xmlns="" on each child element and it is all on one line when you open it with notepad.

I would like the output to look like the attached picture

 Here is my code:

scriptDir = CreateObject("Scripting.FileSystemObject").GetParentFolderName(WScript.ScriptFullName)
Set xmlDoc = CreateObject("Msxml2.DOMDocument.6.0")
Set objRoot = xmlDoc.createElement("CloudEditSettings")
Set xmlns = xmlDoc.createAttribute("xmlns")
xmlns.text = "http://springcm.net/schemas/cloudedit/config/v1"
Set xmlnsxsd = xmldoc.createAttribute("xmlns:xsd")
xmlnsxsd.text = "http://www.w3.org/2001/XMLSchema"
Set xmlnsxsi = xmldoc.createAttribute("xmlns:xsi")
xmlnsxsi.text = "http://www.w3.org/2001/XMLSchema-instance"

objroot.setAttributeNode xmlnsxsi
objroot.setAttributeNode xmlnsxsd
objRoot.setAttributeNode xmlns
xmlDoc.AppendChild objRoot


Set objAuto = xmlDoc.createElement("AutomaticUpdates")
objAuto.text = "true"
objRoot.appendchild objAuto


Set objDiag = xmlDoc.CreateElement("IsDiagnosticLoggingEnabled")
objDiag.text = "false"
objRoot.appendchild objDiag

Set objEmail = xmlDoc.CreateElement("Email")
objEmail.text = "emailaddress.com"
objRoot.appendchild objEmail

Set objAccountID = xmlDoc.CreateElement("AccountID")
objAccountID.text = "11111"
objRoot.appendchild objAccountID


Set objUseSpringCM = xmlDoc.CreateElement("UseSpringCM")
objUseSpringCM.text = "false"
objRoot.appendchild objUseSpringCM

Set 

Open in new window

0
Hi,
I don't know why there is "Type mismatch" error when I run 1st Macro (pressing Alt-F8), vs attached Excel file.
ZCOM-QRL-112N.xlsm
0
Hello experts,
I am looking for a script to replace single space or multiple spaces at files and folders located in a root folder by "-" characters
The idea is to have as variables:
      The character that is going to replace single or multiple spaces
      Drill down mode flag
Powershell & VB Script & Windows Batch approaches are more than welcome.
Thank you for your help.
0
In the attached Excel file, I am trying to get it for the drop downs on the left of the pipe circles, we have RCP and SR26  which reference the tables on the second sheet named Pipe Materials. The issue is that they are only referencing the one column in each table called Pipe Size. I am trying to find how by choosing table and row (RCP or SDR) to have the row information, which is the Thickness and Feet show up in output.

For example if the user choses RCP 15, the output would show Pipe Thickness which is 0.190 and the Pipe Size which is 1.250 in that corresponding row. LOOKUP seems to only do one column.

Any help would be appreciated.
CROSSINGS.xlsx
0
See attached spreadsheet. It is MAC ExcelSample-Equipment-Hunt.xlsx

In the Jobs ws in Column A are a list of jobs. In the List ws is a named range called EQUIP.

In Job ws, column B, is a formula that checks each cell against EQUIP and returns the number of occurrences from the list that are in the cell.

Job ws, column C shows the results I want; Display the actual text (could be more than one) that is in the cell and matches EQUIP.

Solution can be a MAC-compatible Excel formula or a VB macro.

Thanks for your help!
0
I am looking for a solution to search and replace environment variables, either to run locally on each machine, or remotely to achieve the following:-

Each machine may have a number of the following entries in either the system, or user environment variables :
xxx_LM_LICENSE_FILE: 1660@server1
yyy_LICENSE_FILE: 2101@server1
zzz_LICENSE_FILE: C:\LIC\LICENSE.TXT;2100@server1;1655@server1

I need to replace "server1" to licsvr.domain.com, and the xxx, yyy, zzz could be any text
Each machine may have none, some, or all of the above examples
Also as shown there could be multiple entries per line that would need replacing

Therefore the resulting output would be
xxx_LM_LICENSE_FILE: 1660@licsvr.domain.com
yyy_LICENSE_FILE: 2100@licsvr.domain.com
zzz_LICENSE_FILE: C:\LIC\LICENSE.TXT;2100@licsvr.domain.com;1655@licsvr.domain.com

Can someone offer some scripts that might be suitable
Thanks in advance
0
I save a class with various properties into a the current user session like this :
    Public ReadOnly Property UserSession As cUserSession
        Get
            If HttpContext.Current.Session("UserSession") Is Nothing Then
                HttpContext.Current.Session.Add("UserSession", New cUserSession)
            End If
            Return DirectCast(HttpContext.Current.Session("UserSession"), cUserSession)
        End Get
    End Property

Open in new window

I would like to save details in the class to a database before the session is lost.  If I rely upon Session_End in global.aspx, the object is already lost when I go to save it.  I tried to implement idisposable in the cUserSession but this didn't fire.  Whilst I can see how I can manually save the contents of the session by user action or property change, I would like to only do that when the session is about to be lost.

Is this achievable?

Thank you,
0
I have an older Access database (probably created in 2003) that I am opening in newer versions (tried 2010 and 2016). It is a rather complex database. Everything works except one button that calls a vbscript to update data using delete, update, and insert into statements. I cannot say for certain that it worked perfectly in previous versions as I inherited this from a former employee.

The vbscript concatenates data based on information it receives from the queries. The problem I am encountering is that after I click the button, the query inserts special characters instead of the text that it is supposed to grab from one of the other tables.

The fields that are showing correctly in the source table are Long Text data types with Plain Text text format.

I attached a screenshot of a brief sample of the problem I am experiencing.

Special Characters
0
can we use advanced threat protection ( ATP)  from azure AD in order to stop macro and vbs related attacks . through my search i found ATP can be used safe attachments purpose through email.

we have users using on premise active  directory and mailboxes in office 3 65., we dont have on premise exchange server
0
Learn Ruby Fundamentals
LVL 13
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

I have a datetime value that I'd like to specifically format like:

Thursday, May 19, 2019 1:00 PM

The standard FormatDateTime function doesn't fit since it either uses 24-hour format, or includes seconds.

Any suggestions would be appreciated.
0
Hi,

Found this Macro online.  It allows one to paste values only pretty easily.  Using it with a Keyboard Shortcut I attached to it: CTRL+SHIFT+V

Trouble is, it seems to disable UNDO.   So that, one gets just a SINGLE undo.

Is there any way it can be fixed?

Alternatively - My Goal:

Cutting and pasting between two large excel files.  One with LOTS of formatting.  The destination with virtually none.

Hundreds and hundreds of entries to slog through.  Need a keyboard shortcut-supported way to paste from the formatted file just plain values into the unformatted file.

If there's a more efficient way than using the Macro below I'm all ears.

If not, any way to let this Macro stop clearing out the Undo memory?

'==================================================================================================
' ## Paste as values or unformatted text from within or outside of Excel.
' Tip: assign this to a keyboard shortcut: Ctrl+Shift+V
' Developer: Mitch
'==================================================================================================
Sub PasteValues()
    '// first test if pasting from within excel, if an error then
    ' proceed to paste as unformatted text
    On Error Resume Next
    
    '// Paste as values
    Selection.PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    '// Paste as unformatted text
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, 

Open in new window

0
Need to create a macro that will search through a word document and change numbers to a different number format based on their original value. The macro has to be able to search through the entire document and only update the exact number with the change - not any other variant of the number (e.g. change 1.00 to 0001 but do not change 11.00 to 10001 - NOTE: 11.00 should change to 0011 instead). So - I want to search for specific numbers and change them throughout the whole document. The numbers are sequential, but I do not want earlier numbers to change numbers later in the document (see previous example).

There are words associated with each numbered row - see bottom of attached file.

Thanks
0
I'm trying to create a shortcut automatically using vbs script but it refuses to run all the way through  because it throws a char: 2 error - object doesn't support this property or method which happens on this particular line item:

WshShell = CreateObject("Wscript.shell")

Please note attachment that shows error.

I took this code from this website but it hasn't worked out for me.

https://support.microsoft.com/en-us/help/244677/how-to-create-a-desktop-shortcut-with-the-windows-script-host

Public Sub CreateIconToDesktop()

 Dim WshShell
 Dim strDesktop
 Dim oMyShortCut
 WshShell = CreateObject("Wscript.shell")
 strDesktop = WshShell.SpecialFolders("Desktop")
 oMyShortcut = WshShell.CreateShortcut(strDesktop + "\Sample.lnk")
 'oMyShortcut.WindowStyle = 3  &&Maximized 7=Minimized  4=Normal 
 oMyShortcut.IconLocation = "Q:\Dialer\_dbadmin\icons\logocampaign.ico"
 oMyShortcut.TargetPath = "Q:\Dialer\dialercampaigns.accdb"
 'oMyShortCut.Hotkey = "ALT+CTRL+F"
 oMyShortCut.Save

End Sub

Open in new window

0
Need correct syntax or code to format a number using vbs.

Please note the following code which grabs your default MS Access location by formatting the numeric version using the command Function:

The line that contains the error:  Type mismatch: 'Format'

strLnKey = "HKEY_CURRENT_USER\Software\Microsoft\Office\" & Format(sAppVersion & "", "##,##0.0") & "\Access\Security\Trusted Locations\Location"

Please note attached file that shows the listed error.

Basically just want to know how I can either created my own Format function in vbs to do the above or what should be the correct command?
I tried FormatNumber instead of Format but that didn't work.  I tried convert string to integer and then FormatNumber(  … but that didn't work.


    Set oRegistry = GetObject("winmgmts:{impersonationLevel=impersonate}//./root/default:StdRegProv")
    sKey = "Access.Application\CurVer"
    oRegistry.GetStringValue HKEY_CLASSES_ROOT, sKey, "", sValue
    sAppVersion = Right(sValue, Len(sValue) - InStrRev(sValue, "."))
    Set oRegistry = Nothing


    'Specify the registry trusted locations path for the version of Access used
    strLnKey = "HKEY_CURRENT_USER\Software\Microsoft\Office\" & Format(sAppVersion & "", "##,##0.0") & "\Access\Security\Trusted Locations\Location"

Open in new window

ee-vbs-error.PNG
0
Using Classic ASP, VB Classic, and MS SWL Server:  Apologies in advance because I have posted this before but the solutions haven't "worked".  User error maybe but I need to speed up some queries.  For instance, this should be an execute but it throws an error if there is no data returned (I would like it to return a null value or 0 if no data is returned):
    Set rs = Server.CreateObject("ADODB.Recordset")
    sql = "SELECT MeetClass FROM MeetTeams WHERE MeetsID = " & lThisMeet & " AND TeamsID = " & lTeamID
    rs.Open sql, conn, 1, 2
    lMeetClass = rs(0).Value
    rs.Close
    Set rs = Nothing

Open in new window


I need the same solution when populating an array with GetRows.  Here is an example:  If this recordset returns no rows, then how do I dim the array?  If no records are returned I need it to Dim as MeetArray(3, 0) so it doesn't thow an error on the page when listing out meets that don't exist.  I've been given a solution that involved If rs.BOF = rs.EOF Then... but then it never returns any results.
Dim MeetArray
sql = "SELECT MeetsID, MeetName, MeetDate FROM Meets WHERE MeetDate <= '" & Now() & "' AND ShowOnline = 'y' "
sql = sql & "AND Sport = '" & sSport & "' ORDER BY MeetDate DESC"
Set rs = conn.Execute(sql)
MeetArray = rs.GetRows()
Set rs = Nothing

Open in new window


Big event with lots of participants and folks hitting my website for results in about three weeks.  Any help would be much appreciated.
0
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
Expert Spotlight: Joe Anderson (DatabaseMX)
LVL 13
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

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

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.