Writing data to multiple worksheets using VB.Net

[7jgru1]

I'm having troubles writing data to a specific worksheet using VB.Net. I want my program to be able to select a worksheet and write to it. If there are 5 worksheets in a workbook, I want to be able to loop through and write data to each specific worksheet. The code below works for "sheet1", but if I change "shXL = wbXl.Worksheets(1)" to "shXL = wbXl.Worksheets(2)", thinking that this would use "sheet2", it does not work. Any help would be much appreciated.

Dim appXL As excel.Application
Dim wbXl As excel.Workbook
Dim shXL As excel.Worksheet
Dim raXL As excel.Range
Dim xlsFile As String = "\\ssohome\home\ftp\Debbie\test1.xlsx"
' Start Excel and get Application object.
appXL = CreateObject("Excel.Application")
appXL.Visible = True
' Add a new workbook.
'wbXl = appXL.Workbooks.Add
appXL = New excel.ApplicationClass
wbXl = appXL.Workbooks.Open(xlsFile)
'shXL = wbXl.Worksheets("sheet1")
shXL = wbXl.ActiveSheet()
' Add table headers going cell by cell.

With wbXl
shXL = wbXl.Worksheets(1)

shXL.Cells(1, 1).Value = "First Name"
shXL.Cells(1, 2).Value = "Last Name"
shXL.Cells(1, 3).Value = "Full Name"
shXL.Cells(1, 4).Value = "Phone"

End With
wbXl.Save()

 

[Andrzejek]

this would use "sheet2", it does not work" Well, it does work.

Your code does generate in the Sheet2 data in:

[pre]
A B C D
1 | First Name | Last Name | Full Name | Phone
[/pre]



Have fun.

---- Andy

 

[7jgru1]

I think that my problem was that while I was debugging the program, I stopped it before it actually saved the changes. Thanks for your help.

 

[Andrzejek]

When you step thru your code, the line:
[tt]shXL = wbXl.Worksheets(2)[/tt]
does sets it to Sheet2, you just don't see it on the screen because you still look at the Sheet1, but you do write into Sheet2

BTW, I used your code, but I messed with it a little, so I have a Reference to Excel, and this code:

Dim appXL As [blue]New[/blue] Excel.Application
Dim wbXl As excel.Workbook
Dim shXL As excel.Worksheet

Dim xlsFile As String = "\\ssohome\home\ftp\Debbie\test1.xlsx"

appXL.Visible = True
wbXl = appXL.Workbooks.Open(xlsFile)

shXL = wbXl.Worksheets(2)
shXL.Cells(1, 1).Value = "First Name"
shXL.Cells(1, 2).Value = "Last Name"
shXL.Cells(1, 3).Value = "Full Name"
shXL.Cells(1, 4).Value = "Phone"

wbXl.Save()

Have fun.

---- Andy

 

[SkipVought]

Or this

Dim appXL As New Excel.Application

Dim xlsFile As String = "\\ssohome\home\ftp\Debbie\test1.xlsx"

appXL.Visible = True
With appXL.Workbooks.Open(xlsFile)

    With .Worksheets(2)
        .Cells(1, 1).Value = "First Name"
        .Cells(1, 2).Value = "Last Name"
        .Cells(1, 3).Value = "Full Name"
        .Cells(1, 4).Value = "Phone"
    End With
    .Save() 
    .Close
End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

Skip,
That's interesting way of using With statements:

[blue]
With appXL.[/blue]Workbooks.Open(xlsFile)
    With .Worksheets(2)
    ....

Let me get it clear,
Second With statement pretty much ignores [tt]Workbooks.Open(xlsFile)[/tt] part from the first With? And just 'takes' [tt]With appXL.[/tt] ?


Have fun.

---- Andy

 

[SkipVought]

You notice that I INDENT my blocks of code so that it is implied what stuff relates to what block. You can easily see, where stuff begins and where stuff ends and what stuff is related to what.

So everything within the block refers to the WORKBOOK that opens in the With... statement (if the code in the block begins with a DOT), or is indirectly related to the WORKBOOK that opens in the With... statement

With appXL.Workbooks.Open(xlsFile)
   'stuff here is related to the workbook referenced in the previous With
   'directly is it starts with a DOT like [b].Worksheets(2)[/b]
End With

the With block within the With...

With appXL.Workbooks.Open(xlsFile)

    With .Worksheets(2)

    End With

End With

has references the Worksheet(2), which in turh has a reference to the workbook (appXL.Workbooks.Open(xlsFile)).

When I code, I complete ALL my block structures before filling in the details within the block. These blocks include among others...

Sub...End Sub

For....Next

Do...Loop

If...Else...End If

When a new block begins within a block, I indent and enter the entire block structure.

So I recorded a macro that sorts a table

Sub SortAgeingZ()
'
    ActiveWorkbook.Worksheets("Ageing").ListObjects("tAgeing").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Ageing").ListObjects("tAgeing").Sort.SortFields.Add _
        Key:=Range("tAgeing[DaysAtVen]"), SortOn:=xlSortOnValues, Order:= _
        xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Ageing").ListObjects("tAgeing").Sort.SortFields.Add _
        Key:=Range("tAgeing[HrsAtVen]"), SortOn:=xlSortOnValues, Order:= _
        xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Ageing").ListObjects("tAgeing").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Here's how I modified it to be more understandable and better code

Sub SortAgeing()
'
    With ThisWorkbook.Worksheets("Ageing").ListObjects("tAgeing")
        .Sort.SortFields.Clear
        
        .Sort.SortFields.Add _
            Key:=Range("tAgeing[DaysAtVen]"), _
            SortOn:=xlSortOnValues, _
            Order:=xlDescending, _
            DataOption:=xlSortNormal
        
        .Sort.SortFields.Add _
            Key:=Range("tAgeing[HrsAtVen]"), _
            SortOn:=xlSortOnValues, _
            Order:=xlDescending, _
            DataOption:=xlSortNormal
        
        With .Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
End Sub

Hope this helps!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[softhemc]

It's nice to see that I'm not unique in using indentation in this way. Most people at work think that it is superfluous, but, as you say, it makes the code (especially complex code) much easier to read, follow and of course debug.

 

[SkipVought]

I'm rather anal about it.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

Softhemc and Skip, you are not the only ones. I my opinion - that’s the only way to code. When I help my co-workers with their coding problems, first I do align the code (if it is not aligned) no matter what they say. This way I can see what’s going on.
There is no problem in VB.NET As far as I know, there is no way around NOT having the code aligned, IDE does it for you if you want it or not. VBA and VB6 should do it, too.


Have fun.

---- Andy