Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

vb.net excel manipulation

Posted on 2006-06-08
7
Medium Priority
?
606 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:Corey Scheich
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:
Corey Scheich 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:Corey Scheich
ID: 16872551
and you have to reference excell 11 type library
and Imports Microsoft.Office.Interop
0
Industry Leaders: 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:Corey Scheich
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:Corey Scheich
ID: 16873038
No problem thanks for the points
0

Featured Post

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Navigation is an important part of web design from a usability perspective. But it is often a pain when it comes to a developer’s perspective. By navigation, it often means menuing. This is less theory and more practical of how to get a specific gro…
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
The goal of this video is to provide viewers with basic examples to understand and use conditional statements in the C programming language.
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.
Suggested Courses

618 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