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
What does it mean to be "Always On"?
LVL 4
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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
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
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
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
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 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
Independent Software Vendors: We Want Your Opinion
Independent Software Vendors: 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!

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

say I have a log with many lines like the one below

 682.87   8864  SUB-TOTAL: D:\ACCOUNTING\USER V.2

I need to extract only the substring after the "\" character. In this case, "USER V.2".

Would you have an example ? I'm using VBSCRIPT.

Thanks so much.
0
so i n select data from the same folder as needed
0
Hello,

Note:
This is a follow-up to several other threads related to a very cool macro code created by EE Expert gowflow for Excel. The code enables automated intracellular formatting in a spreadsheet.

Please see this thread for a brief explanation and screenshot as well as to obtain the most current version of the code in the attached Excel file.

Question for this thread:

Can the current code be modified so that any number of blank and unrelated columns can be inserted in front or to the left of the Original Text column?

For example, I tried inserting a single column (column A) as shown here:

2017-07-2c19a_EE.png
But after doing so, the Formatted Text appeared in the wrong column (column L) causing it to overwrite part of String 5:

2017-07-2c19b_EE.png
It would be very helpful to be able to freely insert (and/or delete) at the very beginning (ie left of the Original Text column), not just one column, but as many columns as a user might need while working in the spreadsheet. In other words, it would be super if, as an extreme example, one could insert say, 208 columns at the beginning or far left of the spreadsheet (which would move Formatted Text to column HA) without affecting the workings or results of the code.

Is that doable (I hope)?
0
Hi,

I have two Folders G:\LocalPix and G:\ServerPix
LocalPix contains a file called AAAA.JPG

The following VB SCRIPT code Moves AAAA.JPG from LocalPix to ServerPix:

For Each ObjFile in LocalPix.Files
      ObjFile.Copy "G:\ServerPix\"
      ObjFile.Delete
Next


Somewhere I need to include code that displays "AAAA.JPG" in an INPUTBOX so it can be renamed "BBBB.JPG and THEN copied to the ServerPix Folder.

Help is appreciated

Biggles1
0
Î have a visual basic 6 application

I need to be able to play u tube file from the application
by specifying its URL

Does anybody know the syntax for accomplishing this

Thanks

O.A.  Oluwole
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE
LVL 4
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Hello,

What is the quickest/simplest way to determine the position of a certain string of text characters within a text entry in a cell — even if the string of characters occurs more than once?

As an example, suppose the following monologue (from Shakespeare's Henry V) which has a length of 1480 characters is pasted into a single cell and the string of characters to locate is the highlighted statement: "Dishonor not your mothers":

2017-06-29c_EE.png (Note: Monologue is posted below.)


In this case, the string can quickly be determined (to begin at character 907 and have a length of 25 characters) by using the functions =SEARCH() and =LEN().

However, suppose the string of characters occurs multiple times within the text entry but you want to find the position of one particular occurrence.

For example, in the same text entry, suppose you want to find the position of a specific occurrence of the string of four characters: "n th" (ie the letter n followed by a space followed by the letters th). The highlighting in this screenshot shows this string occurs six times in the monologue:

2017-06-29d_EE.png
If a spreadsheet contains the full text entry in one cell, the string of characters to locate (ie "n th") in another cell, and a number designating the desired occurrence (say 5 in this case) in a third cell, how would you determine its position?

Thanks

*Here is the monologue used above:

From Henry V (Act III) by William …
0
Dear experts,

would be much appreciated if you can let me know how I can get systeminfo and IP details of some servers  on my domain. I have listed servers in a .txt file and need the result exported. Servers have 2003 OS installed.  I would like to achieve this with a batch file that I can run from a domain PC to remote servers.  Could anyone share a code for it?  

 thanks in advance!
0
Hi ,

I am trying to use HTA with VBScript and Excel for Task list tracking and have done everything with excel protecting with password. Now I want to convert with HTA file to .exe file and package the excel along with it. Is there any way with VBScript to open file without giving full path in the code. I am new to scripting and a help is highly appreciated.

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open ("D:\Projects\Tracker.xlsx",,,,"xxxx")
Set objsheet = objExcel.ActiveWorkbook.Worksheets(1)

Regards,
Maha.
0
Hi all,

Bit of a strange one this and I wont try and explain the reason I need this as its pretty long winded!

I would like to try and create a logon script, which when triggered executes a wake on lan command (which references a MAC address, ideally in a .csv list or similar) based on which user is logging on.  I hope that makes sense!

Example

User "STEVE" logs on

1.) Logon script is executed

2.) WOL command is triggerd and looks to a CSV file to match up "STEVE" to Mac address 11:11:11:11:11:11

User "DAVE" logs in

1.) Logon script is executed

2.) WOL command is triggerd and looks to a CSV file to match up "DAVE" to Mac address 22:22:22:22:22:22

Would anyone be able to help me achieve this please?

Thank you
0
I have a code that creates a code in a module, but when it does the VBA window flashes up while the code is running.
I have tried 'Application.screenupdating = false', and it does not work, I have also tried    'ThisWorkbook.VBProject.VBE.MainWindow.Visible = False' at the beginning and end of the code but the window still pops up for a short time. I would like to run the code without the vba window being visable at all.

this is the code:



Sub code
   
        Set vbp = Application.VBE.ActiveVBProject
    Set vbc = vbp.VBComponents.add(vbext_ct_StdModule)
    vbc.Name = "CreateButton"
    strCode = "Sub CreateButtons()" & vbNewLine & _
    "ActiveSheet.Buttons.Add(840, 10, 95, 25).Select " & vbNewLine & _
   " Selection.OnAction = ""ClaimsLog""" & vbNewLine & _
    "Selection.Characters.Text = ""Claims Log"" " & vbNewLine & _
  "  With Selection.Characters(Start:=1, Length:=35).Font " & vbNewLine & _
      "  .Size = 10" & vbNewLine & _
   " End With" & vbNewLine & _
 "ActiveSheet.Buttons.Add(840, 40, 95, 25).Select " & vbNewLine & _
   " Selection.OnAction = ""UNM23Report""" & vbNewLine & _
    "Selection.Characters.Text = ""UNM23 Report"" " & vbNewLine & _
  "  With Selection.Characters(Start:=1, Length:=35).Font " & vbNewLine & _
      "  .Size = 10" & vbNewLine & _
   " End With" & vbNewLine & _
 "ActiveSheet.Buttons.Add(840, 70, 95, 25).Select " & vbNewLine & _
   " Selection.OnAction = ""SPLEtool""" & vbNewLine & _
    …
0
Hi Experts. I was able to source this VBA code from a question answered but I need to modify it slightly. I have X number of workbooks with Y number of worksheets in each book. I have information that I would like to compile onto a single master data workbook. The code below is able to gather the data from the first sheet from all the selected workbooks. I need it to do the same for the remaining worksheets. Please help me with this request! Would really appreciate the help. :)

Sub MergeTest()

    Dim SummarySheet As Worksheet
    Dim FolderPath As String
    Dim SelectedFiles() As Variant
    Dim NRow As Long
    Dim FileName As String
    Dim NFile As Long
    Dim WorkBk As Workbook
    Dim SourceRange As Range
    Dim DestRange As Range
    Dim LastRow As Long

    ' Create a new workbook and set a variable to the first sheet.
    Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)

    ' Open the file dialog box and filter on Excel files, allowing multiple files
    ' to be selected.
    SelectedFiles = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", MultiSelect:=True)

    ' NRow keeps track of where to insert new rows in the destination workbook.
    NRow = 1

    ' Loop through the list of returned file names
    For NFile = LBound(SelectedFiles) To UBound(SelectedFiles)
        ' Set FileName to be the current workbook file name to open.
        FileName = SelectedFiles(NFile)

        ' Open the …
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.