?
Solved

vb.net excel manipulation

Posted on 2006-06-08
7
Medium Priority
?
605 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 1500 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
The viewer will learn how to implement Singleton Design Pattern in Java.
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
Suggested Courses

801 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