VB.NET Gembox - Switch Active Spreadsheet

Question

I've looked all over for this and think I'm just hitting a mental brick wall for something simple. But still. I'm writing a quick program to help me with some mileage spreadsheets for different vehicle. Each vehicle has its on worksheet within the spreadsheet, I'm using GemBox in VB.net.

Basically, dending on which button you press it chooses the correct sheet for the corresponding vehicle. I cannot find anything, anywhere that tells me how to choose a different existing sheet as the active worksheet.

This is my test code atm.

Public Sub SetMiles(vehicle As String)

    Dim wb = ExcelFile.Load(file)
    Dim ws = wb.Worksheets.ActiveWorksheet(vehicle) 

    loc = "F12"
    ws.Cells(loc).Value = "800"


End Sub

I don't use vb.net, but if I understand its syntax you need to do something like: `Dim ws = wb.Worksheets("vehicle")` _ `ws.Activate`. — K.Dᴀᴠɪs, Feb 12 '18 at 17:50
@K.Davis, he's trying to select a sheet by a vehicle name passed into the function, not select a sheet named "vehicle". OP, ActiveWorksheet does not accept a parameter, it is called without parameters to return a reference to the current active worksheet. If you want to activate a specific worksheet, I think what you need is `ws = wb.Worksheets(vehicle)` and then call `ws.Activate()` — soohoonigan, Feb 12 '18 at 18:41
Thanks soohoonigan and K.Davis, wb.Worksheets(vehicle) seems to take fine but ws.Activate() is not a valid option. I'll fiddle with it and see if I can figure it out. — Ian Carr, Feb 12 '18 at 19:24

score 1 · Accepted Answer · answered Feb 13 '18 at 10:17

In GemBox.Spreadsheet you don't need to set the sheet as active in order to use it.

In other words, let's say you have an Excel file which has "Sheet1" and "Sheet2". To write into those sheets you can use the following:

Dim wb = ExcelFile.Load(File)

Dim ws = wb.Worksheets("Sheet1")
ws.Cells("A1").Value = "Foo"

ws = wb.Worksheets("Sheet2")
ws.Cells("A1").Value = "Bar"

You can also use the following:

Dim wb = ExcelFile.Load(File)

Dim ws = wb.Worksheets(0)
ws.Cells("A1").Value = "Foo"

ws = wb.Worksheets(1)
ws.Cells("A1").Value = "Bar"

So, I believe that what you need is the following:

Public Sub SetMiles(vehicle As String)

    Dim wb = ExcelFile.Load(File)
    Dim ws = wb.Worksheets(vehicle)

    Loc = "F12"
    ws.Cells(Loc).Value = "800"

End Sub

Last, in case you do need to set some sheet as active, then you can do that with GemBox.Spreadsheet as following:

wb.Worksheets.ActiveWorksheet = wb.Worksheets(vehicle)

However, again GemBox.Spreadsheet doesn't care if the sheet is active or not, you can access and modified it regardless of that. By setting the sheet as active, that sheet will be the first visible one that you see when you open that file in an Excel application.

AJD · Answer 2 · 2018-02-13T06:02:29.057

0

wb.Sheets(vehicle).Activate is the simplest way.

Although I recommend that you also validate the vehicle string to ensure that Sheet actually exists. You can then either ignore, display a message or create a new sheet.

I was assuming that you wanted to activate the sheet so that the user can do manual input. If you are doing automated input (no user interaction), then you are better off not activating the sheet. Something along the lines of:

Public Sub SetMiles(vehicle As String, wb as Workbook, loc as string, Mileage as string)
' passing the values is better encapsulation for OOP purposes
' in your example above, loc="F12", Mileage = "800"
' You have passed the Mileage as a string - but you could also use a numeric value.

' Validate sheet name here if important.
' Validate range name here if important.
    wb.Sheets(vehicle).Range(loc).Value = Mileage

End Sub

Edit: Appears GemBox uses Worksheets instead of Sheets.

edited Feb 13 '18 at 06:02

answered Feb 12 '18 at 18:54

AJD

2,400
2
12
22

Thanks for the response, could you verify for me that you are reffering to VB.net with the GemBox addon and not simply VBA? I do not believe that x.Sheets is a valid option with GemBox. – Ian Carr Feb 12 '18 at 19:21
OK, just looked at this thing called "GemBox" ... does not have `Sheets`, but if `Worksheets` does the same thing, then give that a go instead of `Sheets` to see if you can get the clean code. I note that `Activate` is also not available - so my first suggestion is also out of the window. – AJD Feb 13 '18 at 05:56
I managed to find something in the help file about `Worksheets()` - although the explanation was not too clear and some examples would help. `Worksheets(x)` could be a default implementation of `Worksheets.Item(x)` - which would achieve the same thing as Excel's native `Sheets`. – AJD Feb 13 '18 at 06:01
Thanks for the follow up, it turns out I was making it too complicated. Thanks for the advice! – Ian Carr Feb 13 '18 at 18:25

VB.NET Gembox - Switch Active Spreadsheet

2 Answers2