Error - subscript out of range 1

[ItIsHardToProgram]

Hello,

I have applied a script given to me by SkipVought, who is alot of help always, and I am getting a subscript out of range error on the following line:

        For Each ws In Sheets(Array("BrunoSommaire", "NancySommaire", "JfSommaire"))

The overall code is the following:

Function TransfertExcelAutomation(strSQL As String, _
sEmplacement As String)

On Error GoTo err_Handler

    'Excel object variables
    Dim appExcel As Excel.Application
    Dim wbk As Excel.Workbook
    Dim wks As Excel.Worksheet
    
    Dim sTemplate As String
    Dim sTempFile As String
    Dim sOutput As String
    
    Dim dbs As DAO.Database
    Dim sSQL As String
    Dim IRecords As Long
    Dim iRow As Integer
    Dim iCol As Integer
    Dim iFld As Integer
    Dim ProjectID As String
    
    Dim ws As Worksheet, r As Range
    Dim rst As Recordset
    
    Dim i As Integer
    
    Const cTabOne As Byte = 1
    Const cTabTwo As Byte = 2
    Const cStartRow As Byte = 3
    Const cStartColumn As Byte = 1
    
    iRow = 4
    
    DoCmd.Hourglass True
    
    'Set to break on all errors
    Application.SetOption "Error Trapping", 0
    
    'Start with clean file built from template file
    'sTemplate = sEmplacement & "\BrunoInterface.xls"
    sOutput = sEmplacement & "\BrunoInterface"
    If Dir(sOutput) <> "" Then Kill sOutput
    'FileCopy sTemplate, sOutput
    
    'Create the Excel Application, Workbook and Worksheet and Database object
    Set appExcel = New Excel.Application
    appExcel.Visible = True
    MsgBox (sOutput)
    Set wbk = appExcel.Workbooks.Open(sOutput)
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
    If Not rst.BOF Then rst.MoveFirst
      'For this template, the data must be placed in the appropriate cells of the spreadsheet

    Do
        For Each ws In Sheets(Array("BrunoSommaire", "NancySommaire", "JfSommaire"))
            For Each r In ws.Range(ws.[A4], ws.[A4].End(xlDown))
                If ws.Cells(r.Row, 1) = rst.Fields("IDProjet") Then
                    ws.Cells(r.Row, 9).Value = rst.Fields("Honoraire utilisé")
                End If
            Next
        Next
        
        rst.MoveNext
    
    Loop Until rst.EOF

    rst.Close
    

exit_Here:
'Cleanup all objects (resume next on errors)
Set wbk = Nothing
appExcel.Quit
Set appExcel = Nothing
Set rst = Nothing
Set dbs = Nothing
DoCmd.Hourglass False
i = 0
Exit Function

err_Handler:
    ExportQuery = Err.Description
    Resume exit_Here
           
            
        
End Function

It opens excel fine, the file opens fine and there is a worksheet named accordingly to the array. I am a bit mystified as of why this is not working.

By the way, if you haven't figured it out, im running this from an access front end linked to a backend database.


"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Hi,

Subscript out of range, usually means that a specified object is non-existent; in this case a sheet. So it could be a spelling error, some stray SPACE or SPACES leading or trailing in the acutal sheet name.

You've got to do some digging to find the missmatch.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

You need a reference to workbook before:
[tt]For Each ws In wbk.Sheets(Array("BrunoSommaire", "NancySommaire", "JfSommaire"))[/tt]

combo

 

[ItIsHardToProgram]

Actualy, I first tried removing the array, using Worksheets, and it worked perfectly, so I am assuming indeed, what you have typed, that I had google'd and found, there was something missing trailing etc...

but I copied the array from my worksheet and there was still no joy, I have tried referencing workbooks as combo mentions, and it still dosnt work.

Could it be that there are not enough worksheets in the current workbook, since there are less than 3 worksheets?

Is it possible to use an array with only 2 worksheets?

I am a bit confused as I believe it should work

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Are ALL THREE sheets in the workook that you are referencing?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Per workbook per user there is only one sheet that needs to be used, in the workbook that I was referencing, wich dosnt work since it says that ARRAY can't be managed by workbooks.Sheet

So now I do not reference the workbook, since any way the specific workbook is activated.

In a specific action, only one worksheet needs to be written on (as I am testing)

eventualy over 7 per workbook

The reason I don't want to loop through every worksheet is that everytime it loops through a worksheet it goes through the whole recordset wich takes some time, with 2 worksheets it takes about 4 minutes, and it will be longer with more.

I don't want to narrow down too many problems at once, so I will stick with my subscript problem for now.


"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

You did not answer the question!

Are ALL THREE SHEETS (from the statement in error) in the referenced wotkbook?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Sily me, no they aren't, quite a rethorical question come to think about it.

Thanks again skip, now I understand you can't use array like a select case.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

It was not a rethorical question at all! It is a directly relevent question. Your loop is referencing a NON-EXISTENT SHEET in the workbook you are referencing: hence the ERROR!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

Do you mean rhetorical? Speaking, of course, rhetorically.

However, Skip is not quite correct. It is rhetorical, in that rhetoric deals with "using language effectively and persuasively".

It is, sadly, become known as ONLY meaning "Language that is elaborate, pretentious, insincere, or intellectually vacuous".

In context, VBA is being extremely, and accurately, rhetorical, in that the error is a reference to some that does not exist. In which case, VBA is refusing to be persuaded to do anything, and "Subscript out of range" can be translated as "I have no bloody idea what the heck you are asking about".

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[ItIsHardToProgram]

I did mean rhetorical as in "using language effectively and persuasively".

When I said it was a quite rhetorical question, it meant it was a question that did not necesserely need an answer, as it was informative enough to enlighten me on the prior problem, thus being very informative and persuasive.

But none the less, thank you for this lesson in the philosophie of programmation.

Although this answer is quite far from helping the discussion in programmation grow, I simply thought I would explain my statement so that it isn't misread.

Have a good one Gerry.

P.S. If the error message was "I have no bloody idea what the heck you are asking about", maybe this post would have never took place. Food for thought.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

I have always heard rhetorical questions were defined as any question asked for a purpose other than to obtain the information the question asks.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Hell, none of us is probably getting paid to correctly define the definition of any common word of philosophie and or language, but I enjoy the discussion.

Who knows who holds the answer!

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[fumei]

P.S. If the error message was "I have no bloody idea what the heck you are asking about", maybe this post would have never took place. Food for thought.

I have snacked on that many times, and yes, I personally think that the error message should says just that...maybe a slightly more polite version, but still basically, "Huh?"

"Subscript out of range" may be technically correct, but what the heck is the point if 99% of people seeing that message do not know what it means?

I could write a better error message. Actually, one of the most difficult (yet in some ways most fun) project I had - many years ago - was writing the error messages for a huge system. The book of error messages alone, was 400 pages. Well, not the messages alone; each error message had the full logic possibilities that could cause the error listed with it.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry