VB Script

36K

Solutions

19K

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

Hi My Fellow Scripting Peeps whom I admire because you guys know your Sh1t =)

I would like to have a simple script that i can run every time i need to help friends & colleagues
to backup their data before attempting repairs or formatting.

My reason for a progress bar or progress percentage is so that I have more or less an idea
of how long the process will take or how long before it is finished.

oh by the way... This will be my very first run with robocopy but i have used xcopy and xxcopy in the past.

XXCOPY has a dim future and XCOPY is not as robust as robustcopy(robocopy)

I hope i got all the info you need.
0
Technology Partners: We Want Your Opinion!
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Hi Everyone,
 
I am using one VBS which delete and Insert records from Excel. But need some adjustments here.
For example this script deleting 10 records and inserting 10 records into Excel, but replicating some records again at right side again and again after each run.
And my Excel has 3 multiple Sheet, but I need to insert into a particular sheet i.e. Sheet2
 
after execution I am seeing these defects
1. Legends moved to somewhere else,
2. row 1-6 repeated but right side.
 
Here is my CSV data.
But after second run data got inserts some other place.
 
Name,Location,Phone,Comment1,Comment2,comment3
"ABC","Pune",123,"Expert Value","! Easy","Popular"
"XYZ","Kol",567,"! Expert value",Easy,"!Credit"
"PQR","Mum",234,"NOT value","Value for money","Debit"
"RST","DEL",0,"Value","NO value","N/A"
"Ram","KOL",100,"NO Value","value","N/A"
"XYZ","Kol",567,"! Expert value","!Easy","!Credit"
"qwer","DEL",567,"Expert value","Easy","!Credit"
"cvbn","Pune",567,"! Expert value","!Easy","!Debit"
"rtyu","DEL",567,"! Expert value","Easy","!Credit"
"kllo","Pune",567,"Expert value","NOT Easy","!Bad"

Open in new window


Here is my existing VBS.
 
srccsvfile = Wscript.Arguments(0)
tgtxlsfile = Wscript.Arguments(1)
 
'Create Spreadsheet
'Look for an existing Excel instance.
On Error Resume Next ' Turn on the error handling flag
Set objExcel = GetObject(, "Excel.Application")
'If not found, create a new instance.
If Err.Number = 429 Then  '> 0
  Set objExcel = 

Open in new window

0
Hello,
We are using a special pdf printer. This printer needs a custom defined page size (25.4x25.4mm).
Additional you have to set the TrueType Font to: Download as Softfont.
Now I have the task to distribute this to all users.
We are using NDS and so we have no GPO, but we have Loginscripts and Zenworks as software distribution.
My idea was to deploy a registry script for all users. Unfortunately I can’t found the right key. In these Keys I tried to set it:
HKEY_CURRENT_USER\Printers\DevModePerUser
HKEY_CURRENT_USER\Printers\DevModes2
HKEY_CURRENT_USER\Printers\Settings
I even tried to set it with the “Printing Defaults” of the Printer under:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Print\Printers -> Default DevMode
I did the same in the past with Windows XP and I’m sure that it worked with the Key DevModePerUser.

Any ideas how to set the settings for all users?

Thanks
Christian
0
The following code helps me convert date values into dd-mmm-yyyy, what I'd like to do in addition is then convert the values into text.

So it would stay as dd-mmm-yyyy but the cell value would be text not a date format. I thought one way maybe to text special, but at the moment I have to paste the values into notepad and then pre format the cells so that they enter as text.

Private Sub CommandButton1_Click()
Dim cell As Range
For Each cell In Selection
cell.NumberFormat = "dd-mmm-yyyy"
Next cell

End Sub

Many thanks for your help.
0
Hi Experts,

We have a folder 1 in a network drive.And folder 2 containing 15 different folders say f1 to f15.
Some third party sources put in files which may be upto 150 in number in a day into folder 1.
Each file is named such a way that it had destination folder name within uts name like f1p1data.csv, f3p2data.csv, f1p2data.csv etc.
Here f denotes which folder the file is to put and p denotes priority to be picked up..as in p1 to be picked up first and so on.There is this vbs ript code that runs this logic of putting files one by one into subfolders.The vbscript is present in my local.When ever a file gets picked up into subfolder say f1 for ex..an already existing file say available.txt in f1 folder changes into assigned.txt.A separate process which is scheduled, processes the file in f1(basically moves the file into my local drive and processes) and at last renames the assigned.txt to available.txt.And the process continues.Problem 1: since its  drive, moving file into subfolders is slow
Problem2: since files keep arriving in folder 1, the vbscript seems to be becoming slow in finding the priority file
Please suggest a better way of doing this and combatting the latency.Also suggest a alternate way of looping through as ..files are still getting copied into folder 1 when the vbscript is looping through to find top priority file from one iteration through all files present jn a given time.
0
Hello,

I want to add a csv file to database. There is a column in csv file called as merchant_id same as column in SQL database. I want to update the row in database if the "merchantid" exists in database or if it doesn't exist , we need to add that row from csv file .


Here  

"rs" contains all the columns of the CSV file and its values.

"rsAdd" contains all the columns of Database table(Merchants) and its values.

Set rs = GetRecordSet("C:\upload\new\" & TheNewFileName2)
    'Response.Write "No Errors"
    Set cn = Server.CreateObject("ADODB.Connection")
    cn.ConnectionString="DSN=REWC"
    cn.Open
    i = 0
		TheSql = "SELECT * FROM Merchants"
        Set rsAdd = Server.CreateObject("ADODB.RecordSet")
        rsAdd.CursorType=1
        rsAdd.LockType=3
        rsAdd.Open TheSql, cn
        
    While not rs.EOF
				
			rsAdd.AddNew            
            For Each col in rs.Fields				
                rsAdd(Replace(col.Name,"_","")) = col.Value
            Next		
			
        rsAdd.Update
        rsAdd.Move 0        
        rs.MoveNext
        i = i + 1
        If (i mod 100)=0 Or i=1 Then
            Response.Write i & ","
            If (i mod 2500) = 0 Then
                Response.Write "<br />"
            End If
        End If
    WEND
	
    rsAdd.Close
    Set rsAdd = Nothing	
	End If

Open in new window

0
I am giving my required Excel Template here. As my present scenario this excel will be stored in a fix path. But CSV will generate everyday.

My vb script should execute everyday to collect data from csv and write into this Excel , but small customization needed.

Here First 3 rows are Fixed Header, I need to convert csv and write values in excel from 4th row. but its obvious we have old data there. so it should delete 4th row to 7th row and put csv value as per required place. With proper border also.

Now tell me is it possible to modify my vbs to get this type of output?

srccsvfile = Wscript.Arguments(0)
tgtxlsfile = Wscript.Arguments(1)

'Create Spreadsheet
'Look for an existing Excel instance.
On Error Resume Next ' Turn on the error handling flag
Set objExcel = GetObject(, "Excel.Application")
'If not found, create a new instance.
If Err.Number = 429 Then  '> 0
  Set objExcel = CreateObject("Excel.Application")
End If

objExcel.Visible = False
objExcel.DisplayAlerts = False

'Import CSV into Spreadsheet
Set objWorkbook = objExcel.Workbooks.Open(srccsvfile)
Set objWorksheet1 = objWorkbook.Worksheets(1)

'Adjust width of columns
Set objRange = objWorksheet1.UsedRange
objRange.EntireColumn.Autofit()
'This code could be used to AutoFit a select number of  columns
'For intColumns = 1 To 17
'    objExcel.Columns(intColumns).AutoFit()
'Next

'Make Headings Bold
objExcel.Rows(1).Font.Bold = True

'Freeze header row
With objExcel.ActiveWindow
     .SplitColumn = 0

Open in new window

0
How to send commands to the remote server connected using Mstsc...

I am aware of doing this using Devolutions Remote Desktop Manager and other third party tools but looking for a way to achieve this using windows native tools like PowerShell or any other scripting... I could manage to prepare a tool using PowerShell and windows forms but stuck at logon banner screen where as i am unable to send any commands to the remote server over RDP connection..

any ideas/suggestions ?
0
I have one VBS which convert my csv to excel.
Now my CSV there are some characters like "!".
I want to color Yellow on those cell which are having that character after converting to excel.

Sample.CSV :

Name,Location,Phone,Comment1,Comment2
"ABC","Pune",123,"Expert Value","! Easy"
"XYZ","Kol",567,"! Expert value",Easy"

i.e. after converting to Excel, I need E1 and D2 Cell should be as Yellow

MyScript.vbs : which needs two argument to execute
cscript C:\Test\MyScript.vbs \\C:\Test\Sample.CSV \\C:\Test\Sample.xlsx

'======================================
' Convert CSV to XLS
'
' arg1: source - CSV path\file
' arg2: target - Excel path\file
'======================================

srccsvfile = Wscript.Arguments(0)  
tgtxlsfile = Wscript.Arguments(1)  

'Create Spreadsheet
'Look for an existing Excel instance.
On Error Resume Next ' Turn on the error handling flag
Set objExcel = GetObject(,"Excel.Application")
'If not found, create a new instance.
If Err.Number = 429 Then  '> 0
  Set objExcel = CreateObject("Excel.Application")
End If

objExcel.Visible = false
objExcel.displayalerts=false

'Import CSV into Spreadsheet
Set objWorkbook = objExcel.Workbooks.open(srccsvfile)
Set objWorksheet1 = objWorkbook.Worksheets(1)

'Adjust width of columns
Set objRange = objWorksheet1.UsedRange
objRange.EntireColumn.Autofit()
'This code could be used to AutoFit a select number of  columns
'For intColumns = 1 To 17
'    …
0
Every month I have to go through several text files after paying bills online to send to the CFO to show all the bills that have been paid. I have tried to get him to accept a print of confirmation page but instead he wants it in the format of file 2 below (ee-online-pmts-result.txt). So I copy the results from the web page, past it to a text file and then clean it up removing all the "junk" not needed. As such is there a way I can read the first text file (ee-online-pmts.txt) and then have a list of variables to parse that if found would be removed in such a way that the result will be what the 2nd text file looks like? For instance if  "Print this activity" and two carriage returns (enter, enter) are found it will remove it? Thanks!
ee-online-pmts.txt
ee-online-pmts-result.txt
0
[Live Webinar] The Cloud Skills Gap
LVL 4
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Hi
I have a requirement in VB script to fetch data from oracle and copy nearly 100K records in excel sheet,
so far i was able to write this code to fetch 65K records, but not able to fetch 100k records in excel sheet.

Sub ConnectTOOracle()
 
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim mtxData As Variant
    
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    cn.Open ( _
    "User ID=userhr" & _
    ";Password=passwdhr" & _
    ";Data Source=HR_TEST" & _
    ";Provider=OraOLEDB.Oracle")
    
    rs.CursorType = adOpenForwardOnly
 rs.Open ("SELECT LEVEL level_a,  COL1,  1 COL2,  2 COL3,2 COL4,2 COL5 FROM   ( SELECT 1 COL1 FROM dual   UNION ALL   SELECT 3  FROM dual  )  CONNECT BY LEVEL <= 16"), cn
    
    mtxData = Application.Transpose(rs.GetRows)
    
    Sheets("Sheet222").Activate
    
   Range("A2").Resize(UBound(mtxData, 1) - LBound(mtxData, 1) + 1, UBound(mtxData, 2) - LBound(mtxData, 2) + 1) = mtxData

    'Cleanup in the end
    Set rs = Nothing
    Set cn = Nothing
  
End Sub

Open in new window


any help will be appreciated.

Thanks,
0
This macro lists the number of items next to each item when clicking on the top row of a column.

I 'think' this macro may depend on a function and on sheet level code.

I'd like to make it work as a single macro if possible.

Assistance is greatly appreciated.
mListbox.xlsb
0
I had this question after viewing Expand macro to include freezing of top row..

How can I expand this macro to include automatically setting the width of the column to the width of the header row?

Sub DeleteEmpty()
    Dim objSheet As Worksheet
    Dim objRange As Range
    Dim lngLastRow As Long
    Dim lngRow As Long
    Dim lngCol As Long

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

    ' Work with ActiveSheet and UsedRange
    Set objSheet = ActiveSheet
    Set objRange = objSheet.UsedRange

    ' Delete empty rows
    For lngRow = objRange.Rows.Count To 1 Step -1
        If WorksheetFunction.CountA(objRange.Rows(lngRow)) = 0 Then
            objRange.Rows(lngRow).EntireRow.Delete
        End If
    Next

    Set objRange = objSheet.UsedRange
    lngLastRow = objRange.Rows.Count

    ' Delete empty columns (look at data below header in row 1)
    For lngCol = objRange.Columns.Count To 1 Step -1
        If WorksheetFunction.CountA(objSheet.Range(objSheet.Cells(2, lngCol), objSheet.Cells(lngLastRow, lngCol))) = 0 Then
            objRange.Columns(lngCol).EntireColumn.Delete
        End If
    Next

    ' Freeze top row
    ActiveWindow.SplitColumn = 0
    ActiveWindow.SplitRow = 1
    ActiveWindow.FreezePanes = True

    ' Remove any auto filter
    If ActiveSheet.AutoFilterMode Then ActiveSheet.UsedRange.AutoFilter

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

Open in new window

0
I am currently trying to enforce a password policy with a minimum of 8-10 characters, a capital, number, and symbol.  Typically I use a regex in asp.net, however I am trying to do this for a classic asp site prior to form submission.  I was hoping to find something that would pop up when it doesn't match the criteria or something where it starts with a disabled "Submit" button and once you hit the criteria the button would become "active".

The only thing I have working now is:
If  Len(Request("password")) < 6 Then
	ErrorMessage = ErrorMessage & Server.URLEncode("You need to enter a Password of at least 6? characters.\n\n")
End IF

Open in new window

which makes the user have 5 or more characters in their password.
0
I have passed string from vb variable to crystalReport TextObject, and when i create a formula and browse for that TextObject to calculate ,it's not available to calculate, how can this done?

Thanks in advance.
0
We staretd getting this error since we moved to Office 2010. This particular VB application has been using Excel 2003 to send emails with an Excel attachments that has pictures.
Since we moved to 2010, the Excel shows this error message instead of the pictures.

Upon research, I figured that 2010 fails to identify linked pictures. I am now trying to modify my code to paste the pictures instead of inserting. Please help.

Old code (That used to work)

xlWs1.Pictures.Insert("C:\pictures\test.jpg", pStart, pEnd).Select
                          xlWs1.Shapes("Picture " & Tt).ShapeRange.LockAspectRatio = msoFalse
                          xlWs1.Shapes("Picture " & Tt).ShapeRange.Height = 288#
                          xlWs1.Shapes("Picture " & Tt).ShapeRange.Width = 324#

New Code (Trying to get it to work)
                         
                          xlWs.Shapes.AddPicture "C:\pictures\test.jpg", False, msoTrue, pStart, pEnd, 324#, 288#
0
We currently operate within a Citrix environment and publish a number of web applications by point to Internet Explorer with the corresponding website as the parameter.

We face an issue currently whereby if the browser is closed, the session remains active.

We need the session to logoff if the browser is closed.

With a previous version of IE (8 perhaps, but not entirely sure) we scripted this, however in current versions of IE this no longer works.

I guess a script that launches IE, and then monitors for its closure before issuing a logoff command would be ideal, but am open to alternative suggestions if there are better ways to achieve what we are looking for.
0
The code below was written a long time ago to delete empty rows.  It should be extended to delete empty columns and rows.  Assistance is greatly appreciated.
Sub DeleteRowNoInclude()

'This function should allow removal of rows that do not include a specified text.
'Update20140618

Dim xRow As Range
Dim rng As Range
Dim WorkRng As Range
Dim xStr As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
xStr = Application.InputBox("Text", xTitleId, "", Type:=2)
Application.ScreenUpdating = False
For i = WorkRng.Rows.Count To 1 Step -1
    Set xRow = WorkRng.Rows(i)
    Set rng = xRow.Find(xStr, LookIn:=xlValues)
    If rng Is Nothing Then
       xRow.Delete
    End If
Next
Application.ScreenUpdating = True
End Sub

Open in new window

0
Im trying to read only first letters of each in the given sentence

Example  "Hello World"

i want out put as this : HW
Please help
0
Ready to get started with anonymous questions?
LVL 9
Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Dears,

I am trying to sort the texts of one particular node (<Name>) of a XML file in ascending order using VB Scripting.

Say, I am using a XML file (xyz.xml) with content as shown below:

<?xml version="1.0" encoding="utf-8"?>
<!--Informations Vendor="Country S.A."-->
<India>
      <Statelist>
            <Name>2122</Name>
            <Name>1011</Name>
            <Name>Karnataka</Name>
            <Name>Goa</Name>
            <Name>Assam</Name>
      </Statelist>
</India>

The expected output is:

<?xml version="1.0" encoding="utf-8"?>
<!--Informations Vendor="Country S.A."-->
<India>
      <Statelist>
            <Name>1011</Name>
            <Name>2122</Name>
                <Name>Assam</Name>
            <Name>Goa</Name>
            <Name>Karnataka</Name>            
      </Statelist>
</India>

Can someone please help me here?

Best regards
Chethan Bangera
0
Hi All,

I have another file which needs reading and converted to CSV.

I have attached the file and sample needed , anyhelp is appreciated.

Thank you

A
AP070717.txt
APSAMPLE.xlsx
0
I am new to excel vbscript.
I have XLSM file spreadsheet called A.xlsm that contains macros written in it.  I am trying to read this A.xlsm file from another xlsm file but this seems not working.

Please help.

Thanks in advance.

Srini
0
Hi All i have this file which i want to convert to csv very simple i just need all the columns.

Account Number  &  Name                   Telephone No.   Contact          Credit Limit               Last Cash  Branch/Ref/Type   Date   Due Date Order Number             Current      30-Days      60-Days      90-Days     120-Days     150-Days   STE  Balance                 Unapplied

I have attached the file.

Thank you for your help
AR070717.txt
0
Hi experts, I think there's a short cut method in reading text file. My code below looks so messy and I want to modify it in a clean and short way. My goal is to read all text files (Sales.text) since when the user add the sales it would rename to Sales (1).txt , Sales (2).txt and so on. Any help please.

Private Sub Command2_Click()
    Dim iPath As String
    Dim FileSystemObject As Object
    
    Set FileSystemObject = CreateObject("Scripting.FileSystemObject")
    '1
    If FileSystemObject.FileExists("C:\Users\Administrator\Downloads\Sales.txt") Then
        iPath = "C:\Users\Administrator\Downloads\Sales.txt"
        doPrinting iPath
        FileSystemObject.DeleteFile iPath
    End If
    '2
    If FileSystemObject.FileExists("C:\Users\Administrator\Downloads\Sales (1).txt") Then
        iPath = "C:\Users\Administrator\Downloads\Sales (1).txt"
        doPrinting iPath
        FileSystemObject.DeleteFile iPath
    End If
    '3
    If FileSystemObject.FileExists("C:\Users\Administrator\Downloads\Sales (2).txt") Then
        iPath = "C:\Users\Administrator\Downloads\Sales (2).txt"
        doPrinting iPath
        FileSystemObject.DeleteFile iPath
    End If
    '4
    If FileSystemObject.FileExists("C:\Users\Administrator\Downloads\Sales (3).txt") Then
        iPath = "C:\Users\Administrator\Downloads\Sales (3).txt"
        doPrinting iPath
        FileSystemObject.DeleteFile iPath
    End If
    '5
    If 

Open in new window

0
I'd like to keep all of my custom function on one worksheet, so I can glance over them and edit them quickly. For example, on my "Custom Functions" sheet I may have a cell with a text value of  'MAKENAME', and a cell next to it that reads 'firsname & " " & lastname'. then, somewhere else in the Workbook I could have a cell value of =MAKENAME(B1, B2), which is the function actually executing a concatenation.
Is this possible?

Thanks in advance,
Zumpoof
0

VB Script

36K

Solutions

19K

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.