Announcement

Collapse
No announcement yet.

Data into excel with VBS and batch file

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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

    Check out our new reputation system. Click on the "star" under the post!
    _______________________________________________

    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

    _______________________________________________

    Comment


    • #3
      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

      Comment


      • #4
        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

        Comment


        • #5
          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

          Check out our new reputation system. Click on the "star" under the post!
          _______________________________________________

          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

          _______________________________________________

          Comment


          • #6
            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!!!

            Code:
            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, 19:58.

            Comment


            • #7


              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

              Check out our new reputation system. Click on the "star" under the post!
              _______________________________________________

              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

              _______________________________________________

              Comment

              Working...
              X