1

I am running a few modules of code in access and am writing data into Excel. When I write the first time, data gets written properly. But again when I try, the new data is written on top of the old data. What should I do to insert a new sheet?

My existing code is

Dim objexcel As Excel.Application
Dim wbexcel As Excel.Workbook
Dim wbExists As Boolean
Dim objSht As Excel.Worksheet
Dim objRange As Excel.Range                                    
Set objexcel = CreateObject("excel.Application")
On Error GoTo Openwb
wbExists = False
Set wbexcel = objexcel.Workbooks.Open("C:\REPORT1.xls")
Set objSht = wbexcel.Worksheets("Sheet1")
objSht.Activate
wbExists = True

Openwb:              
On Error GoTo 0
If Not wbExists Then
    objexcel.Workbooks.Add
    Set wbexcel = objexcel.ActiveWorkbook
    Set objSht = wbexcel.Worksheets("Sheet1")
End If
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
tksy
  • 3,429
  • 17
  • 56
  • 61
  • yes that s it if sheet 1 is populated then goto sheet 2 if sheet 2 is populated then go to sheet 3 and so on. – tksy Aug 05 '09 at 10:27

1 Answers1

3

I think that the following code should do what you want. It's very similar to yours, except it uses the return values from the .Add methods to get the objects you want.

Public Sub YourSub()
    Dim objexcel As Excel.Application
    Dim wbexcel As Excel.Workbook
    Dim wbExists As Boolean
    Set objexcel = CreateObject("excel.Application")

    'This is a bad way of handling errors. We should'
    'instead check for the file existing, having correct'
    'permissions, and so on, and actually stop the process'
    'if an unexpected error occurs.'
    On Error GoTo Openwb
    wbExists = False
    Set wbexcel = objexcel.Workbooks.Open("C:\REPORT1.xls")
    wbExists = True

Openwb:
    On Error GoTo 0
    If Not wbExists Then
        Set wbexcel = objexcel.Workbooks.Add()
    End If

    CopyToWorkbook wbexcel
EndSub

Private Sub CopyToWorkbook(objWorkbook As Excel.Workbook)
    Dim newWorksheet As Excel.Worksheet
    set newWorksheet = objWorkbook.Worksheets.Add()

    'Copy stuff to the worksheet here'
End Sub
Ant
  • 5,150
  • 2
  • 33
  • 41
  • This would still be doing the same function. when i repaetedly run the code data will bve copied into sheet1 only – tksy Aug 03 '09 at 11:44
  • 1
    I won't vote your answer down, because the part relevant to the question is correct, but I think using On Error Resume Next and then On Error GoTo 0 for a block of code is very bad practice. If there's an anticipated error that you know it's safe to ignore, write an error handler that throws away that error. Otherwise, you run the danger of ignoring significant errors that should not be discarded. – David-W-Fenton Aug 04 '09 at 19:50
  • Oh I agree - I copied that bit of the code from tksy's code. I could have changed it to check for the file existing and permissions being set and so on, but decided that he knew best. You are right though, so I'll edit my answer to note that this isn't a good way of doing it. – Ant Aug 05 '09 at 09:54
  • Wouldn't it be simpler to check whether the workbook exists before attempting to open it? If Len(Dir("C:\REPORT1.xls")) = 0 Then create the workbook – HansUp Aug 05 '09 at 13:53
  • What if it exists, but the user hasn't got access to it? I personally think it's simpler to just use a blank workbook, but I suppose tksy wants to append to the existing one. – Ant Aug 05 '09 at 15:06
  • @HansUp, Ant: I believe you can use the MS scripting runtime to check the user's read/write access on a file. – Andrew Scagnelli Aug 06 '09 at 13:31
  • @A. Scagnelli: sorry I didn't notice your comment. How about if you've got the workbook open in some other application already? There are quite a few things to check if you're going to be thorough. – Ant Sep 09 '09 at 12:16