Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Data into excel with VBS and batch file

  1. #1
    Join Date
    15th July 2010
    Posts
    3

    Data into excel with VBS and batch file

    Hi,

    I had a requirement where i need to write data into a xls sheet (around 3-5 columns in it) with 'N' number of rows using a batch file.

    Basically, i need to run a batch file (.bat extn in dos) which does some processing and later save the processed data into a xls sheet under the specified columns. There can be more than 30 rows in it and there should not be any limit of number of rows.

    Is this possible using batch application (.bat extn)? Or is there any other way to do the same?

    A sample script would be more helpful.

    Thanks in advance.

    Sateesh

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,512

    Re: save data in excel sheet from batch file

    Hi - welcome to the forum.

    A batch file can only contain comments and native Windows command line instructions. I dont think there is a command line that will allow you to write contents/data directly into Excel.

    However since windows 98, all PC's come with a scripting language called VB Scipt, which provides far more power and functionality to do things like this. It is based off the COM model.

    http://en.wikipedia.org/wiki/VBScript

    You could write some VB Script and launch this VB Script from a batch file. There is plenty of examples of VB Script available to download from the web. A quick google for "VB Script to Excel" throws up this example, among others:

    http://www.rlmueller.net/Write to Excel.htm
    and
    http://techrepublic.com.com/5208-734...260319&start=0
    Hope this helps.
    Ger
    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

  3. #3
    Join Date
    3rd March 2008
    Posts
    8

    Re: save data in excel sheet from batch file

    Hi Sateesh,

    I do this by clicking on file in exce,l open filename, adjust to show all files select the batch file and a little import wizard opens up. If you start the macro recorder before you do this you should be good to go.

    Regards

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    15th July 2010
    Posts
    3

    Re: save data in excel sheet from batch file

    Thanks Ger for your tip. It worked and i could write the data into a xls file using vbscripts. Thanks once again - Sateesh

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,512

    Re: save data in excel sheet from batch file

    Good Sateesh - glad it worked - since I'm no expert on VB Script myself, would you mind posting your VBScript as a solution for myself and other users?

    Ger
    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

  6. #6
    Join Date
    15th July 2010
    Posts
    3

    Re: save data in excel sheet from batch file

    Sure Ger. Please find the vbscript code below to write data into a xls shet... Please suggest any improvement to this code, if needed!!!

    VB:
    Option Explicit 
    Dim strExcelPath, objExcel, objSheet 
    Const xlExcel7 = 39 
     ' Spreadsheet file to be created.
    strExcelPath = "c:\test\testExcel.xls" 
     ' Bind to Excel object.
    On Error Resume Next 
    Set objExcel = CreateObject("Excel.Application") 
    If (Err.Number <> 0) Then 
        On Error Goto 0 
        Wscript.Echo "Excel application not found." 
        Wscript.Quit 
    End If 
    On Error Goto 0 
     ' Create a new workbook.
    objExcel.Workbooks.Add 
     ' Bind to worksheet.
    Set objSheet = objExcel.ActiveWorkbook.Worksheets(1) 
    objSheet.Name = "User Groups" 
     ' Populate spreadsheet cells with user attributes.
    objSheet.Cells(1, 1).Value = "Column1" 
    objSheet.Cells(1, 2).Value = "Column2" 
    objSheet.Cells(1, 3).Value = "Column3" 
    objSheet.Cells(1, 4).Value = "Column4" 
    objSheet.Cells(2, 1).Value = 1 
    objSheet.Cells(2, 2).Value = 2 
    objSheet.Cells(2, 3).Value = 3 
    objSheet.Cells(2, 4).Value = 4 
     
     ' Format the spreadsheet.
    objSheet.Range("A1:D1").Font.Bold = True 
    objExcel.Columns(1).ColumnWidth = 20 
    objExcel.Columns(2).ColumnWidth = 30 
    objExcel.Columns(3).ColumnWidth = 30 
    objExcel.Columns(4).ColumnWidth = 30 
     ' Save the spreadsheet and close the workbook.
     ' Specify Excel7 File Format.
    objExcel.ActiveWorkbook.SaveAs strExcelPath, xlExcel7 
    objExcel.ActiveWorkbook.Close 
     ' Quit Excel.
    objExcel.Application.Quit 
     ' Clean Up
    Set objSheet = Nothing 
    Set objExcel = Nothing 
    Wscript.Echo "Done" 
    
    
    The code in batch file (.bat) is given below...
    @echo off
    cscript testvbscript2.vbs
    Echo On
    Last edited by Ger Plante; July 23rd, 2010 at 19:58.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,512

    Re: save data in excel sheet from batch file

    Thats terrific - thanks for posting. This will be very useful for other members.

    Good job.
    Ger
    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Run Dos Batch File
    By dddorian in forum EXCEL HELP
    Replies: 1
    Last Post: August 30th, 2007, 22:00
  2. Trying to run a Batch file using VBA.
    By Michael Garrett in forum EXCEL HELP
    Replies: 28
    Last Post: September 30th, 2004, 02:12
  3. Replies: 4
    Last Post: July 1st, 2004, 21:45
  4. excel macro from a batch file?
    By nospam in forum EXCEL HELP
    Replies: 8
    Last Post: February 22nd, 2003, 02:09

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno