CODEWITHSUNDEEP
excelvb.netgembox-spreadsheet
Ian Carr
Ian Carr

Reputation: 11

VB.NET Gembox - Switch Active Spreadsheet

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

Upvotes: 0

Views: 646

Answers (2)

Mario Z
Mario Z

Reputation: 4381

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.

Upvotes: 1

AJD
AJD

Reputation: 2438

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.

Upvotes: 0

Related Questions