Solved

vb.net excel manipulation

Posted on 2006-06-08
7
595 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
  • 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PYTHON: Updating local variable in .TPL file 24 137
Setup specific permissions in Microsoft Access 9 56
countHi challenge 25 98
base64 decode encode 12 119
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…
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.
The viewer will learn how to implement Singleton Design Pattern in Java.
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…

919 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now