Go Premium for a chance to win a PS4. Enter to Win

x

VB Script

36K

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

Sheet1 contains 9 columns - the key ID is the Postcode. Each postcode isbe repeated several times with different  contents in the cells on each row for that postcode number.

Sheet2 contains 2 columns - Postcode, Local Zone. Each postcode is repeated several times with different numbers in the Local Zone column.

The requirement is to copy the row by postcode from Sheet1, into the row with the same postcode in Sheet2, and to repeat the copy as many times as there are different Local Zone numbers, and the create a new set of the same postcode/local zones for the next postcode from Sheet1.  Each row in Sheet1 is created in Sheet2 for as many times as the LocalZone number occurs.

The example file shows the first postcode contents from Sheet1, copied manually into Sheet2.
Example-masthead-to-local-zone-2017.xlsx
0
Keep up with what's happening at Experts Exchange!
LVL 11
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

I found the following vbscript below that can extract the Windows Product key from a Windows 7 machines. This works well for me as I do NOT wish to use a 3rd party tool for this purpose. My problem is that I need the output of the vbscript (which is displayed in a message box), echo'ed from the batch file. Just to clarify, the main file will be a batch file which then calls this vbscript and echo's the Product Key from the batch file (The reason I need to do this from a batch file is because this portion of the batch file is part of a much larger portion of the same batch file that get executed from a Remote Management System when then extracts system details from the machines and populate UDF fields).

Source of vbscript
https://gist.github.com/eyecatchup/d577a2628666a0ad1375

Actual VB code
' VBS Script to get the Windows(R) 7 Product Key from a PC's registry.
'
' Save the VBScript as "getWin7Key.vbs" somewhere on your Windows7 PC.
' Now, when you double-click the local script file an alertbox pops up
' displaying the product key stored in the machine's Windows registry.

Set WshShell = WScript.CreateObject("WScript.Shell")

KeyPath = "HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\DigitalProductId"
MsgBox ExtractKey(WshShell.RegRead(KeyPath))

Function ExtractKey(KeyInput)
    Const KeyOffset = 52
    i = 28
    CharWhitelist = "BCDFGHJKMPQRTVWXY2346789"
    Do
        Cur = 0
        x = 14
        Do
            Cur = Cur * 256
            Cur = 

Open in new window

0
All, I have a formula that kind of works but for some reason its pulling the names from the array into the first column.

=VLOOKUP($A2,Sheet2!$A$5:$K$175,MATCH(Sheet3!B$1,Sheet2!$B$4:$J$4,0))

I am just trying to match the totals under the dates in the VLOOKUP table that I made. The only problem in my first column it matches the names instead of giving me the totals, but after the first column the totals appear. I cannot share the data since its confidential.
0
Im stuck on trying to get two values (a string, and a checkbox) from an Excel user form to another Sub. Receiving 'Object Required' on the User Form code. I have researched and tried different methods to no avail. My codes are below:

User Form:
Public Sub UserForm_Initialize()
    EditForm.Show
    Dim MgrNotes As String
    Dim Deactivate As Boolean
    MgrNotes = tbMgrNotes.Value
    Deactivate = cbDeactivate.Value
End Sub
Public Sub btnSave_Click()
Call OnSave(MgrNotes, Deactivate)
End Sub

Open in new window


General Module:
Sub OnSave(MgrNotes, Deactivate)

Dim cnn As New ADODB.Connection
Dim iRowNo As Integer
Dim StockUnit As String
     
With Sheets("Sheet1")
    cnn.Open "my connection string"
        
    'Read Stock Unit # From Selected Row and Run UPDATE Queries
    iRowNo = ActiveCell.Row
    StockUnit = .Cells(iRowNo, 1)
    cnn.Execute ("UPDATE Table SET fMgrNotes = '" & MgrNotes & "' WHERE (StockUnit = '" & StockUnit & "') UPDATE Table SET ActiveFlag = 0 WHERE " & Deactivate & " = TRUE AND (StockUnit = '" & StockUnit & "')")
    
    'Close All and Cleanup
    cnn.Close
    Set cnn = Nothing
    Set iRow = Nothing
             
    End With
End Sub

Open in new window


The Call statement line is where the error occurs.

Thanks in advance.
0
For years we have been using the following vb code in a Classic ASP page to retrieve data from a software product.
To boost performance and get away from Classic ASP, I need to ASHX C# equivalent of the following  

    //set h = CreateObject("MSXML2.SERVERXMLHTTP")
    //h.open"POST", "http://localhost/isapi/webapi.dll"  //this is a software provided dll that accepts xml objects and returns xml response objects
    //h.setOption(2) = 4096
    //h.send(doc)
    //response.Write("<br><textarea rows=\"20\" cols=\"60\">" + h.responseText + "</textarea>");    
    //for each response in h.responseXml.selectnodes("//Response")
    //response.Write("<br><textarea rows=\"20\" cols=\"60\">" + response.xml + "</textarea>");
    //next
runner.txt
0
Sorry People here!

I know you answered this question somehow some , but now there is one bit not clear according to Microsoft instructions below:

To write code that can work in both new and older versions of Office you can use a combination of the new VBA7 and Win64conditionalCompiler Constants. The Vba7 conditional compiler constant is used to determine if code is running in version 7 of the VB editor (the VBA version that ships in Office 2010). The Win64 conditional compilation constant is used to determine which version (32-bit or 64-bit) of Office is running.

#if Vba7 then
'  Code is running in the new VBA7 editor
     #if Win64 then
     '  Code is running in 64-bit version of Microsoft Office
     #else
     '  Code is running in 32-bit version of Microsoft Office
     #end if
#else
' Code is running in VBA version 6 or earlier

Does it means that us who are using Ms access 2016

Then full code should be as follow:

#If VBA7 Then
'* My 64-bit declarations
Declare PtrSafe Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
#Else
'* My 32-bit declarations
Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
#End If
#IF Win64 THEN
  Declare PtrSafe Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal _
   lpBuffer As String, nSize As Long) As LongPtr
#Else
  Declare Function GetUserName Lib …
0
Hi,
I am looking at a macro that copies one worksheet (values and formats) from Workbook A into a new workbook (Workbook B). The sheet in question contains a cell (FO9) that I would like the new file name to be called.
I currently have the copy and paste function working ok, and all data has copied over correctly to the new workbook.  

What I’m after is for the new workbook to “save as” automatically as an XLSM file using the name in cell FO9 in workbook B. The user must however be asked where to save it (save as). I then need this workbook to close and for the user to return back to cell AO64 in Workbook A, with Sheet 1 being hidden again, and for Sheet 2 to then become the active worksheet again.

The code I currently have is:

Sub SaveAsA0()
Application.ScreenUpdating = False
Range("D3").Select
Sheets("Sheet 1").Visible = True
Sheets("Sheet 2").Visible = True
Sheets("Sheet 1").Select

ActiveSheet.Copy
    With ActiveSheet.UsedRange
         .Copy
         .PasteSpecial xlValues
         .PasteSpecial xlFormats
         End With
    Application.CutCopyMode = False
    
    
End Sub

Open in new window

0
I created a simple Macro that cleans some data from an excel file that generated from ServiceNow every month.  The Macro simply formats some fields, creates columns, splits data in one column to two columns and sorts the data.  Here is the current code, I know the code isn't optimized but I am under a time constraint to get this done.  

Sub MIMCleanup()
'
' MIMCleanup Macro
'
' Keyboard Shortcut: Ctrl+k
'
    Columns("A:A").Select
    Selection.NumberFormat = "mm/dd/yyyy"
    Columns("B:B").Select
    ActiveWorkbook.Worksheets("Page 1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Page 1").Sort.SortFields.Add Key:=Range("B2:B16") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Page 1").Sort
        .SetRange Range("A1:O16")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("E:E").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Range("E1").Select
    ActiveCell.FormulaR1C1 = …
0
I have office 2007 Pro installed on a Win 10 PC.  The program runs fine on my PC from within Access and as a package install where Excel 2007 opens with the file.

When I install the program on a Win7 PC with Office 10 Small Business (so no Access 2010) the program runs normally until the last macro where it dies in the function OpenExcel() at the last step that I have shown in the code.

If I comment out this line (and all the other lines that use Excel) the program ends normally (but doesn't open the .xlsm spreadsheet, copy over data into cells).  I have not enclosed the rest of the code as I began commenting out 1 line at a time to see where the code fails, and the last line I am showing generates the Action Failed 2950 error and causes the program to close when you click on Stop All Macros.  The Macro has 4 Run Commands in it, all work except this macro that points to this function.

I am using the Microsoft Excel 12.0 Object Library

I am installing the package off the root drive, C:\PRISM\ which I have also added in Access as a trusted location.

I am guessing but wonder if it has to do with the Excel 12.0 Object Library, I assume that it would be brought over in the runtime modules but maybe not.  I can't test it on my machine because it works on mine.

Function OpenExcel()

Dim DB As DAO.Database
Dim Fil As DAO.Recordset
Dim AppExcel As Excel.Application
Dim PrsMod As Excel.Workbook
Dim PrsFrm As Form

Set DB = CurrentDb()
Set Fil = …
0
this is the script:
Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run C:\Users\root\Music\folder\mytool.exe
WScript.Sleep 1000
WshShell.SendKeys "{LEFT}"
WshShell.SendKeys "{LEFT}"
WshShell.SendKeys "{TAB}"
WshShell.SendKeys "{SPACE}"

Open in new window


I want this:
I want to run as administrator and then i do this?
i open mytool.exe and ..left arrow twice then i press TAB then i press space to start automatically my exe
but doesn't work i receive this error, can someone help me please?

script: C:Users\root\Desktop\start1.vbs
Line: 2
Char: 16
Error: Expected statement
Code: 800A0400
Source: Microsoft VBScript compilation error

Open in new window

0
Free Tool: Port Scanner
LVL 11
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

I want to run CCMClean.exe from a server on a remote PC, I need to remove SCCM from the PC and remove the CCM folders under Windows. If someone has a script (Powershell or VB) or even a bat file that would be great.

Just in case I was not clear. CCMClean is on a server  \\servername\client\ccmclean.exe  
And I want to run that on a remote PC to remove SCCM.
0
Hello SCCM Guru's!

We have 10,500 clients

We are trying to deploy a task sequence that has a startmod.vbs (vb script) file in it. The TS works perfectly every time, until we add the VB file at the end.

I've tried 10 different ways according to the internet findings, but so far, nothing is working. The script never even runs. All the other items run before it, but once it gets to the .vb script, it errors out.

Any clue as to how to deploy a .vb script in a task sequence?

Thank you so much for your kind assistance.

-Mac
0
Hello,

Please review the code in the attached spreadsheet.  I am not experienced in VB code and hoping someone can assist.  When this code runs it creates new workbooks based on data in column C.  Each new workbook is password protected and the worksheets are also protected.  I need the code to be updated so for each new workbook the individual worksheets are locked but with no password on the sheet so the end-user can easily unlock for editing.  

Greatly appreciate any assistance.
EE-Sample.xlsm
0
Hi everyone -

Is there a way in Server 2012 R2 to create a login script (most likely a VB Script, I would think) that will map network drives based on whether the user is a member of a particular security group?

For example, if I have users that are members of a domain and they should have access to specific corporate data only if they are managers or executives, can I create a share, assign the members of the "Managers" and "Executives" groups permissions to that share, and then have the login script map the necessary drive letter only if the user who's logging in is a member of one of those groups?

Any info would be greatly appreciated.

Thanks much!  :-)
0
I am trying to read in all columns in a SQL table to a VB checklistbox item and then check the ones that have a "checked" value of 1 in SQL.
Everything is working except it throws an exception when I get to the line where it is supposed to check one.
I am a pretty basic skilled programmer.
Any help is appreciated.
The line below that throws the exception is CheckedListBox1.SetItemCheckState(Index, CheckState.Checked)
Here is my code:
  Dim connectionString As String
        Dim cnn As SqlClient.SqlConnection
        Dim cmd As SqlClient.SqlCommand
        Dim sql As String
        Dim reader As SqlClient.SqlDataReader
        Dim i As Integer = 0
        Dim td As DateTime = DateTime.Now
        Dim Newdate = td.ToString("yyyy-MM-dd")

        connectionString = "Data Source=SQLSERVER\SQLEXPRESS;Initial Catalog=DBName;Persist Security Info=True;User ID=FakeID;Password=FakePassword"
        sql = "select CONVERT(VARCHAR(10), AddTime, 120),PatientMRN,PatientName,Checked,Completed from CarePlanOCR where CONVERT(VARCHAR(10), DateColumn, 120) = '" + Newdate + "'" + " order by CONVERT(VARCHAR(10), AddTime, 120)"
        cnn = New SqlClient.SqlConnection(connectionString)
        Try
            cnn.Open()
            cmd = New SqlClient.SqlCommand(sql, cnn)
            reader = cmd.ExecuteReader()
            reader.Read()
            While reader.Read()
                Dim checkedstate As Integer = reader.Item(3)
                'Add based on checked state
            

Open in new window

0
Hi Experts,

I have some tasks that currently users must do it manually in daily bases, however since this is prone to errors, the manager would like it should get automated done via code.

1- Look for specific file in default download folder that name starts with Tables_TodaysDate_LatestNumber.Zip (See attached).
2- Extract it to specific folder (C:\Application)
3- Delete old File C:\Application\Tables.MDB
4- Rename extracted file (something like Tables_2017-Oct-30_2230.mdb) to Tables.MDB

Would prefer it done either by calling some DOS commands from VBA or just using native VB functions if possible.

Thanks in advance.
Capture.PNG
0
Greetings .VBS script guru's!

I'm trying to write a FOR loop that continually loops every 15 minutes (in the background) until the user clicks the "OK" button. If the user clicks the "cancel" button, then the VBS script will run in the backgroup and pop-up again every 15 minutes until the user clicks the "OK" button (which then performs an action). I've tried a DO, and WHILE and a FOR loop, without any success.

Any ideas how I could accomplish this?

Thank you so much for your expert assistance!

-Mac
0
Hi everyone, I need a bit of clarity when it comes to the difference between module and forms in vb
0
Hello,

The attached sample has an existing macro which creates different workbooks based on the data in column C.  I am hoping someone can help add to this code by making the following updates:

1.  For every new workbook created the column width is expanded to 15
2.  For every new workbook created columns B, C, F, G H, I, O, P, T, U, V, X, AE, AJ and AK are hidden and locked.
3.  For every new workbook created the entire workbook is password protected.  Sample password can be:  password.

Any assistance would be greatly appreciated!  I am not very proficient in VB code and hoping some of you Excel/VB ninjas can figure this out.

Thank you.
EE-Sample.xlsm
0
Concerto Cloud for Software Providers & ISVs
LVL 5
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Hello,

Please see attached example.  I'd like to run a macro that will create multiple duplicate Excel workbooks based on the number listed in column C starting at C14 and looping until the end.

Looking at the Planning Worksheet - I basically want the code to gather all identical numbers starting at C14 and copy the information over to a new workbook and then continuously loop through creating new workbooks for each new number until the end of the column.  

So in the example, it would create a duplicate workbook for 000001 which would contain the data in rows 14 and 15.  Then it would create another duplicate workbook for 000002 which would contain the data in rows 16 and 17. It would continue to do this until there are no more numbers in column C.  Rows 1 - 13 will remain the same for all workbooks.  I would need all formulas to carry over to the new workbooks.

Ideally, if the two other worksheets in the workbook (Instructions and 2017 Band) can also carry over to the new workbooks it would be great but not an issue if that cannot be done.

No idea how to go about this and hoping some of you Excel/VB ninjas out there can help provide the necessary code.

Thanks!
EE-Example.xlsx
0
here the script i have till now, but not working well,:

Set wshShell = CreateObject("WScript.Shell") 

Do 
    ret = wshShell.AppActivate("Idle timer expired") 
    If ret = True Then 
        wshShell.SendKeys "{ENTER}" 
        Exit Do 
    End If 
    WScript.Sleep 500 
Loop 

Open in new window

0
I am  running a vb outlook script to move emails to a personal folder. Currently, the vb script calls a recursive complicated function to do this.  I want to hard code the actual personal path to replace the function call ( getfolderbyname("test").  What is the syntax to move an email to a particular folder.

current code:
Item.Move GetFolderByName("test")  
          Item.Save
want to replace with item.move "folder path/test"  ?
0
I have a worksheet with four columns.  The fourth column has values separated by commas that belong to the same row (columns A - D).  I would to take each value from the fourth column and create a new row adding the values from columns A - D for each row.

Example:
Norrland      hus1      2017      1, 4, 7, 9-12, 23
Norrland      hus2      2018      5, 9, 21-23, 33

Expected result:
Norrland      hus1      2017      1
Norrland      hus1      2017      4
Norrland      hus1      2017      7
Norrland      hus1      2017      9
Norrland      hus1      2017      10
Norrland      hus1      2017      11
Norrland      hus1      2017      12
Norrland      hus2      2018      5
Norrland      hus2      2018      9
Norrland      hus2      2018      21
Norrland      hus2      2018      22
Norrland      hus2      2018      23
Norrland      hus2      2018      33
0
Hi Experts,

I'm trying to automate a script that sends a daily email to registered users - my script is below - I want Task Scheduler to run the script, however in testing in CMD I get the following error -

Microsoft VBscript Runtime Error Variable is undefined : 'Server'

Here's my code -

Option Explicit

' FileName="Connection_odbc_conn_dsn.htm"
' Type="ADO" 
' DesigntimeType="ADO"
' HTTP="false"
' Catalog=""
' Schema=""
Dim MM_SITE_STRING
MM_SITE_STRING = "Provider= SQLOLEDB; Data Source=217.111.111.111,1433; Database=DBASE;uid=USER;pwd=XXXXX;"
' create recordset of email recipients - this runs a SP on the Server

Set cmdAlertRecipientList = Server.CreateObject ("ADODB.Command")
cmdAlertRecipientList.ActiveConnection = MM_SITE_STRING
cmdAlertRecipientList.CommandText = "dbo.SITEDailyJobsbyEmailRecipientList"
cmdAlertRecipientList.CommandType = 4
cmdAlertRecipientList.CommandTimeout = 0
cmdAlertRecipientList.Prepared = true
set AlertRecipientList = cmdAlertRecipientList.Execute
AlertRecipientList_numRows = 0

If not AlertRecipientList.eof then
arrAlertRecipientList = AlertRecipientList.GetRows()
End if 

AlertRecipientList.Close() 'Clean Up
Set AlertRecipientList = Nothing 'Clean Up
Set cmdAlertRecipientList = Nothing 'Clean Up

'Loop through recordset to get ID for each recipient 

If IsArray(arrAlertRecipientList) AND UBound(arrAlertRecipientList,2) >= 0 Then

'Now for each record a subroutine i.e for each record do something.
Dim 

Open in new window

0
I have a code that creates a multiple select popup list that uses the information in the data validation cell to populate, however, I cannot for the life of me get the popup to display one set of data, then after I click ok, for it to change to another data separated by semi colons. Please see the attached file.
code-in-popup.txt
code-that-works.txt
0

VB Script

36K

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.