Advertise Here

VB Script

36K

Solutions

Content

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.

VB Code excel

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.

6 Comments

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.

2 Comments

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

8 Comments

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

…

37 Comments

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 ?

4 Comments

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
' …

10 Comments

LVL 25

Question by

Lionel MM

2017-07-17Solved

Clean up a Text File

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

12 Comments

[Live Webinar] The Cloud Skills Gap

LVL 4

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,

2 Comments

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

9 Comments

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

16 Comments

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.

2 Comments

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.

1 Comment

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#

2 Comments

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.

15 Comments

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

1 Comment

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

2 Comments

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"?>

<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"?>

<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

3 Comments

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

36 Comments

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

VB Script

4 Comments

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

58 Comments

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

…

5 Comments

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

2 Comments

First
Prev
1
2
3
4
5
6
7
Next
Last

VB Script

36K

Solutions

Content

19K

Contributors

Course Of The Month

VB Script

Related Topics

VB Script

Related Topics