Solved

vb.net excel manipulation

Posted on 2006-06-08
7
601 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
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: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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
tenRun challenge 28 106
withoutTen challenge 14 137
sumHeights  challenge 17 79
Bartender label printing - switch on and off graphics 3 72
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
This video teaches viewers about errors in exception handling.

756 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