[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How do I add values to the Excel  Column Headers?

Posted on 2009-04-16
18
Medium Priority
?
249 Views
Last Modified: 2012-08-13
I am using MDE 2003 to create a Excel file that will be a report. I want to add values to the column header. I have code that adds the calues to row 1 now.

                For intColumn = 0 To ds.Tables(0).Columns.Count - 1
                    .Cells(1, intColumn + 1).Value = ds.Tables(0).Columns(intColumn).ColumnName.ToString
                Next
 How Do I add the values to the column headers?
0
Comment
Question by:expertsexchangehunter
  • 9
  • 9
18 Comments
 

Author Comment

by:expertsexchangehunter
ID: 24158574
Here is more of the code
            With myExcel
                .SheetsInNewWorkbook = 1
                .Workbooks.Add().Worksheets(1).Select()
                'TO Display the column name in the the excel file.  
                For intColumn = 0 To ds.Tables(0).Columns.Count - 1
                    .Cells(1, intColumn + 1).Value = ds.Tables(0).Columns(intColumn).ColumnName.ToString
                Next
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24158768
You can't. The only thing you can do is hide the column and row identifiers (you can't just do the column headers) and use Row 1 as a header instead.
Regards,
Rory
0
 

Author Comment

by:expertsexchangehunter
ID: 24158891
OK. The code adds values in the first row and then this code adds the row values. Except it adds them in the first row also. How do I add the actual values starting 1 row below the row headers which are in row 1?

                   For intRow = 0 To ds.Tables(0).Rows.Count - 1
                    For intColumnValue = 0 To ds.Tables(0).Columns.Count - 1
                        .Cells(1, intColumnValue + 1).Value = ds.Tables(0).Rows(intRow).ItemArray(intColumnValue).ToString
                    Next
                Next
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24159140
You could change it to:
 

For intRow = 0 To ds.Tables(0).Rows.Count - 1
                    For intColumnValue = 0 To ds.Tables(0).Columns.Count - 1
                        .Cells(intRow + 2, intColumnValue + 1).Value = ds.Tables(0).Rows(intRow).ItemArray(intColumnValue).ToString
                    Next
                Next

Open in new window

0
 

Author Comment

by:expertsexchangehunter
ID: 24159451
Ok perfect. Can you show me how can I display the spreadsheet from the program without creating the file? Thanks.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24160330
What do you mean by "without creating the file"?
0
 

Author Comment

by:expertsexchangehunter
ID: 24160357
I want to execute the code described above and then have the spreadsheet displayed.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 24161400
Add:
MyExcel.visible = true

0
 

Author Comment

by:expertsexchangehunter
ID: 24161992
I added that and it looks like it's trying to start  Excel as seen on the browser where you see the bar when things are loading. Here is the code and am I missing anything?

        Try
            Const conControlNumber As Int32 = 4 'SQL column number column

            Dim sqlConnection As New SqlConnection(CStr(Application("myConnString")))

            Dim strsql = Session.Item("strQRY")

             Dim da As New SqlClient.SqlDataAdapter
            da.SelectCommand = New SqlClient.SqlCommand(strsql, sqlConnection)
            da.Fill(ds)

            Dim intColumn, intRow, intColumnValue As Integer
 
            Dim dt As DataTable = ds.Tables("Qry")
            strControlNumber = ds.Tables(0).Columns(conControlNumber).ToString()
            Dim myExcel As Object
            myExcel = CreateObject("Excel.Application")

            With myExcel
                .SheetsInNewWorkbook = 1
                .Workbooks.Add().Worksheets(1).Select()
                'TO Display the column name in the the excel file.  
                For intColumn = 0 To ds.Tables(0).Columns.Count - 1
                    .Cells(1, intColumn + 1).Value = ds.Tables(0).Columns(intColumn).ColumnName.ToString
                Next
                'For displaying the column value row-by-row in the the excel file.        
                For intRow = 0 To ds.Tables(0).Rows.Count - 1
                    For intColumnValue = 0 To ds.Tables(0).Columns.Count - 1
                        .Cells(intRow + 2, intColumnValue + 1).Value = ds.Tables(0).Rows(intRow).ItemArray(intColumnValue).ToString
                        '                        .Cells(intRow + 1, intColumnValue + 1).Value = ds.Tables(0).Rows(intRow).ItemArray(intColumnValue).ToString
                    Next
                Next

                 .ActiveWorkbook.Close()
            End With

            myExcel.visible = True


            sqlConnection.Close()

        Catch ex As Exception
            ' Display error message
         End Try
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24162052
You should remove this line:
.ActiveWorkbook.Close()
as it's closing the workbook you just created, but other than that it looks OK to me.
0
 

Author Comment

by:expertsexchangehunter
ID: 24162250
I removed ActiveWorkbook.Close() and it doesn't even try now.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24162329
Doesn't even try what?
This is the line that starts Excel:
  myExcel = CreateObject("Excel.Application")


the line I asked you to remove simply closes the workbook you just created so I don't see the point of it.
0
 

Author Comment

by:expertsexchangehunter
ID: 24162408
I can tell because it finishes the code and displays the browser with no indication that anything is loading and there are not any windows opening. Where as before with the workbook close I could see some activity in the browder loading area at first. Except the bar for loading would fill about halfway and stop there.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24162557
Where exactly does the browser come into this?
0
 

Author Comment

by:expertsexchangehunter
ID: 24162592
The app is a Web app and uses the browser to run the app.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24162758
Ah. I know nothing about Excel in a web app, I'm afraid.
Rory
0
 

Author Comment

by:expertsexchangehunter
ID: 24162791
Thats bad. Any suggestions on what I can do for help now?

0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24163164
Post a new question? Since it's not part of your original question anyway...  :) You'll want to aim more at .Net and Web related zones than Excel though for that.
Regards,
Rory
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

873 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