Xtreme Visual Basic Talk

Xtreme Visual Basic Talk (http://www.xtremevbtalk.com/)
-   .NET Office Automation (http://www.xtremevbtalk.com/-net-office-automation/)
-   -   Adding worksheets (http://www.xtremevbtalk.com/-net-office-automation/327453-adding-worksheets.html)

george_m 12-31-2014 04:18 AM

Adding worksheets
 
Hey,
I've problems with adding worksheets in excel through VB code.

I've created excel file with VBA code - BeforeSave event where few new worksheets are created (MAKRO.xlsm). The event is triggered only if Cell(1,1) value is 0 and excel file alone works fine.

I've also prepared program in VB, where this file is opened and managed - code below. Problem is that new worksheets are not created when triggering BerforeSave event in excel file.


Code:

  Dim APP As New Excel.Application
    Dim BOOK As Excel.Workbook
    Dim SHEET As Excel.Worksheet


    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        BOOK = APP.Workbooks.Open(Application.StartupPath & "\MAKRO.xlsm")

    End Sub

    Private Sub Form1_FormClosed(sender As Object, e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed

        SHEET = APP.Sheets.Item(2)

        SHEET.Cells(1, 1).value = 0
        APP.ActiveWorkbook.Save()
        APP.Quit()

    End Sub


george_m 01-02-2015 05:00 AM

I've also noticed when running VB program that everything in BeforeSave event in Excel file is done on the last edited sheet (sheet number 2) and not on the sheet that should be created (sheet number 3). This is code from VBA:


Code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If Cells(1, 1).Value = 0 Then
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "3"
ActiveSheet.Cells(1, 1).Value = 10
End If

End Sub

Value 10 appears on the second sheet not on the new one (third). I'm missing something in declaring in VB code...

Cerian Knight 01-02-2015 02:27 PM

I'm not sure if this helps, but I recall that you are limited in terms of what you can do in the BeforeSave event within VBA code. If that is the case, you may have to delay (or cancel) the close and/or save operations until you are sure the sheet has been properly added to the workbook. Then perform the save/close again (avoiding any redundant execution in your VB and/or VBA code).

In any case, creating a sheet may not guarantee that the newly created sheet is the active sheet. Set the ActiveSheet explicitly, if you are not sure (by using .Activate).

george_m 01-05-2015 03:38 AM

I've already thought about that - I've linked ActiveWorkbook.Save() to Button2_Click just to give Excel enought time before closing but the result was the same. New Worksheets are not added, the rest of VBA code is executed on the last sheet of Excel file.

TheRealTinTin 01-05-2015 06:10 AM

I could be wrong but it looks to me like you're setting cell A1 on the wrong sheet. when you initiate the FormClosed event you tell the program to set cell A1 on Sheet 2 to 0, but when you're testing the value of cell A1 in the BeforeSave event you simply check cell A1 of the activesheet - so if the workbook opens on Sheet 1 it will never meet the criteria and therefore never add the worksheet.

I can't justify why 10 is appearing on the second sheet though - was it perhaps already there? Or is it maybe a 0 instead of a 10?

If it is a 10, then you can probably ignore the above as the event is clearly triggering, although I'd still change the code to make it more robust :)

george_m 01-07-2015 11:05 AM

OK, I simplified the code, there is only 1 sheet in Excel at the start. After Button1_Click value of cell(1, 1) is changed to 1 (at the start the value is 0). That should trigger creation of a new sheet named "2" just before the saving of Excel file. When I open Excel file after closing application value of the cell is 1 but new sheet is not created. :confused:

VB code
Code:

Public Class Form1

    Dim APP As New Excel.Application
    Dim BOOK As Excel.Workbook
    Dim SHEET1 As Excel.Worksheet

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        BOOK = APP.Workbooks.Open(Application.StartupPath & "\MAKRO.xlsm")
        SHEET1 = APP.Sheets.Item(1)

        SHEET1.Cells(1, 1).value = 1
        APP.ActiveWorkbook.Save()

    End Sub


    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click

        APP.Quit()

    End Sub

End Class

VBA code
Code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If Cells(1, 1).Value = 1 Then

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "2"

End If

End Sub


TheRealTinTin 01-12-2015 03:40 AM

Hi george, apologies for the delay only just getting a chance to get on.

OK two things I would try. Firstly, I'd try using the BeforeClose event rather than BeforeSave. Although you're explicitly saving the workbook, I'd just test it to see if that triggers. The second thing I'd try is opening the Workbook manually, changing the value of [A1] and then saving/closing the workbook to see if it saves the new worksheet. If it does, then it has something to do with .NET and if it doesn't, then it's the code and (probably) where it's triggered.

I'll need to wait until I'm home to test .NET as we still use legacy VB in work but if it doesn't work I'll give it a bash later.


All times are GMT -6. The time now is 02:32 PM.

Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Search Engine Optimisation provided by DragonByte SEO v2.0.15 (Lite) - vBulletin Mods & Addons Copyright © 2017 DragonByte Technologies Ltd.
All site content is protected by the Digital Millenium Act of 1998. Copyright©2001-2011 MAS Media Inc. and Extreme Visual Basic Forum. All rights reserved.
You may not copy or reproduce any portion of this site without written consent.