Solved

vb.net excel manipulation

Posted on 2006-06-08
7
603 Views
Last Modified: 2011-09-20
I am calling a DTS package that populates an excel spreadsheet with the data from a table in sql server.  After the excel spreadsheet is populated, I would like to be able to programatically insert a new row at row 1, and in cell A1 I would like to to give the title of the spreadsheet ("Acme Accounting Report").  Any ideas on how to use excel objects from VB.net to get this done?

Thanks,

Ryan
0
Comment
Question by:dchau12
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 13

Expert Comment

by:Corey2
ID: 16867458
Something close to this should work.  I am not exactly sure all the declarations are exact I am coding by memory.

Imports Microsoft.interop.office

Dim xl as new excel.Application
Dim wkbk as excel.workbook = xl.open(FilePath)
dim sht as excel.worksheet = wkbk.activesheet
Sht.name =  "Acme Accounting Report"
Dim Rng as excel.range = sht.range("A1")

rng.insertrow(xlAbove)

rng.value2 = "Acme Accounting Report"
0
 
LVL 13

Accepted Solution

by:
Corey2 earned 500 total points
ID: 16872540
I verified it it should be

    Sub test()


        Dim xl As New excel.Application
        Dim wkbk As excel.workbook = xl.open(FilePath)
        Dim sht As Excel.Worksheet = wkbk.ActiveSheet
        Dim Rng As Excel.Range = sht.Range("A1")

        sht.Name = "Acme Accounting Report"
        Rng.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown, False)
        rng.value2 = "Acme Accounting Report"
    End Sub
0
 
LVL 13

Expert Comment

by:Corey2
ID: 16872551
and you have to reference excell 11 type library
and Imports Microsoft.Office.Interop
0
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!

 

Author Comment

by:dchau12
ID: 16872801
Hey corey,

This looks close.  I am trying to figure out why I do not have the .open method for the excel workbook object.  I do have the "Microsoft Office excell 11.0 object library" library referenced with the correct import in the page (Imports Microsoft.Office.Interop).  I will keep messing with it.

Thank you so much for looking into this.

Ryan
0
 

Author Comment

by:dchau12
ID: 16872990
Corey,

Really Really close.

Here is the final product:
            '**************  Open the excel sheets.  *********************************************
            ' Rename the tabs, insert a new row in row 1 and insert the name of the sheet in row 1

            Dim xl As New Excel.Application
            Dim wkbk As Excel.Workbook = xl.Workbooks.Open(DateStampedName)
            Dim sht As Excel.Worksheet = wkbk.ActiveSheet
            Dim Rng As Excel.Range = sht.Range("A1")

            sht.Name = "Need To Contact"
            Rng.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown, False)
            Rng = sht.Range("A1")
            Rng.Value2 = "Need To Contact"
            wkbk.Close(DateStampedName)
            '***************************************************************************************

First, I changed the method of .open to Workbooks.open.  Then, when restated the range to "A1" with the line:
Rng = sht.Range("A1")
This is because when the new row was inserted, the range automatically advanced to make it "A2".  

This works great!  Thank you so much for your time!

Ryan
 
0
 
LVL 13

Expert Comment

by:Corey2
ID: 16873009
Interesting it didn't give me a compiler error but it wasn't available directly try this
xl.Workbooks.Open(Filename)
0
 
LVL 13

Expert Comment

by:Corey2
ID: 16873038
No problem thanks for the points
0

Featured Post

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sumDigits challenge 9 178
FizzBuzz challenge 9 87
Change to event 1 135
Copy Files - Python 7 133
If you haven’t already, I encourage you to read the first article (http://www.experts-exchange.com/articles/18680/An-Introduction-to-R-Programming-and-R-Studio.html) in my series to gain a basic foundation of R and R Studio.  You will also find the …
The purpose of this article is to demonstrate how we can use conditional statements using Python.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question