Adding worksheets
Adding worksheets
Adding worksheets
Adding worksheets
Adding worksheets
Adding worksheets Adding worksheets Adding worksheets Adding worksheets Adding worksheets Adding worksheets Adding worksheets Adding worksheets
Adding worksheets Adding worksheets
Adding worksheets
Go Back  Xtreme Visual Basic Talk > > > Adding worksheets


Reply
 
Thread Tools Display Modes
  #1  
Old 12-31-2014, 05:18 AM
george_m george_m is offline
Newcomer
 
Join Date: Mar 2012
Posts: 20
Default 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
Reply With Quote
  #2  
Old 01-02-2015, 06:00 AM
george_m george_m is offline
Newcomer
 
Join Date: Mar 2012
Posts: 20
Default

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...
Reply With Quote
  #3  
Old 01-02-2015, 03:27 PM
Cerian Knight's Avatar
Cerian KnightAdding worksheets Cerian Knight is offline
Polymath (in disciplina)

Super Moderator
* Expert *
 
Join Date: May 2004
Location: Michigan
Posts: 4,199
Default

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).
__________________
I got all the answers wrong on the GLAT, apparently even #9 (where I put a period in the middle of the box and labeled it 'singularity ripe for rapid inflation').
Reply With Quote
  #4  
Old 01-05-2015, 04:38 AM
george_m george_m is offline
Newcomer
 
Join Date: Mar 2012
Posts: 20
Default

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.
Reply With Quote
  #5  
Old 01-05-2015, 07:10 AM
TheRealTinTin's Avatar
TheRealTinTin TheRealTinTin is offline
Junior Contributor
 
Join Date: Nov 2008
Location: Glasgow, UK
Posts: 383
Default

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
__________________
Artificial Intelligence is no match for natural stupidity
Reply With Quote
  #6  
Old 01-07-2015, 12:05 PM
george_m george_m is offline
Newcomer
 
Join Date: Mar 2012
Posts: 20
Default

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.

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
Reply With Quote
  #7  
Old 01-12-2015, 04:40 AM
TheRealTinTin's Avatar
TheRealTinTin TheRealTinTin is offline
Junior Contributor
 
Join Date: Nov 2008
Location: Glasgow, UK
Posts: 383
Default

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.
__________________
Artificial Intelligence is no match for natural stupidity
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Adding worksheets
Adding worksheets
Adding worksheets Adding worksheets
Adding worksheets
Adding worksheets
Adding worksheets Adding worksheets Adding worksheets Adding worksheets Adding worksheets Adding worksheets Adding worksheets
Adding worksheets
Adding worksheets
 
Adding worksheets
Adding worksheets
 
-->