Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Array error - VBA 3

Status
Not open for further replies.
ItIsHardToProgram

ItIsHardToProgram

Technical User
Mar 28, 2006
946
CA
Hey again, I don't want to double post, but I made the other thread messy and this is a different issue (I believe they are both related).

I am getting random errors when running the code. When I say random I mean the following:

When I open access,

First time I run it, it runs smooth.

Second time I run it, I get a missing array error, all the arrays are present.

Third time I run it, no problem, and I can keep it at that forever.

:( Here is a quick glance at the code, wich is already present in my other post:

Code: 
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 sDAte 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 iWbk As Integer
    Dim ProjectID As String
    
    Dim ws As Excel.Worksheet, r As Excel.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 & "\MarcInterface.xls"
'    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 = False
    'MsgBox (sOutput)
    Set wbk = appExcel.Workbooks.Open(sOutput)
    iWbk = Workbooks.Count
    
    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 Workbooks(iWbk).Sheets(Array("JfSommaire", "MaSommaire", "MartinSommaire", "BrunoSommaire", "JimSommaire", "NancySommaire", "GuillaumeSommaire"))
            For Each r In ws.Range(ws.Range("A4"), ws.Range("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
sDAte = Date

Workbooks(iWbk).SaveAs FileName:=sEmplacement & "\" & "MarcInterface" & "-" & sDAte & ".xls"
Workbooks(iWbk).Close
MsgBox ("Processed")
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

Thanks for any help!


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

You assign am Excel application object and a workbook object and then you do not use it!
Code: 
    Set wbk = appExcel.Workbooks.Open(sOutput)

'[b]why are you even doing this. Don't you already have THE WORKBOOK open that you want??? wbk  ???[/b]
    iWbk = [b]appExcel.[/b]Workbooks.Count
    
    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 [b]wbk.[/b]Sheets(Array("JfSommaire", "MaSommaire", "MartinSommaire", "BrunoSommaire", "JimSommaire", "NancySommaire", "GuillaumeSommaire"))
            For Each r In ws.Range(ws.Range("A4"), ws.Range("A4").End(xlDown))

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Because I tried doing

Code: 
Wbk.SaveAs FileName:=sEmplacement & "\" & "MarcInterface" & "-" & sDAte & ".xls"

And it said that the following object could not take the specific command :(

I also tried appExcel.Wbk.Saveas

If I put wbk instead of Workbook(iWbk) it should work?


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



Is wbk the correct workbook?

Is wbk the workbook you are working on?

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Yes and no.

For example, I can be working on a workbook, then I run the macro, itll work in background I can still work on the workbook im working on.

The workbook it opens is closed.

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



The workbook it opens is closed.
Click to expand...
Does you procedure not OPEN the workbook each time you run it? Which implies that it ought to be CLOSED before the procedure runs. Hmmmmm?

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Yes, thats exactly what I am saying! We are having a hard time understanding each other!

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.
 
Upvote 0 Downvote
Anyway, allways use full qualified excel objects:
[!]appExcel.[/!]Workbooks

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
PH has hit the nail on the head. Your several unqualified references to "Workbooks" will cause all sorts of oddities due to implicit instantiation - VBA trying to help you, but ending up causing you exactly the sort of trouble you are seeing.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website
 
Upvote 0 Downvote


...which was also mentioned in the previous thread.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
I am sorry that I am being redondant and quite thick headed!

I actualy din't really link the fact that because he corrected one line of reference, every refence had to be qualified.

An error I will not reproduce (try not to)

But as always, your all alot of help, thank you.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.
 
Upvote 0 Downvote
I will message on wenesday with confirmation that everything is working fine (Wich I believe it will be.)

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.
 
Upvote 0 Downvote
So I have referenced everywhere I have Workbooks(). with AppExcel.Workbooks() and I am still getting the missing array error.

I get this error on the second run, if I open access, run the code, it works, second time I run it with the same instance, I get the missing array error.

Here is my code:

Code: 
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 sDAte 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 iWbk As Integer
    Dim ProjectID As String
    
    Dim ws As Excel.Worksheet, r As Excel.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 & "\MarcInterface.xls"
'    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 = False
    'MsgBox (sOutput)
    Set wbk = appExcel.Workbooks.Open(sOutput)
    iWbk = Workbooks.Count
    
    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 appExcel.Workbooks(iWbk).Sheets(Array("JfSommaire", "MaSommaire", "MartinSommaire", "BrunoSommaire", "JimSommaire", "NancySommaire", "GuillaumeSommaire"))
            For Each r In ws.Range(ws.Range("A4"), ws.Range("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
sDAte = Date

appExcel.Workbooks(iWbk).SaveAs FileName:=sEmplacement & "\" & "MarcInterface" & "-" & sDAte & ".xls"
appExcel.Workbooks(iWbk).Close
MsgBox ("Processed")
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

Thanks for your help!

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.
 
Upvote 0 Downvote
Code: 
iWbk = [b]AppExcel.[/b]Workbooks.Count

Oups, no array error on second run...

Thank you, and sorry for the hard time I gave you all. Have a star!

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

K
  • Locked
  • Question
How to resolve define worksheets array
Replies
1
Views
691
combo
combo
thanhthanh12
  • Locked
  • Question
Help vba
Replies
3
Views
601
combo
combo
Andrzejek
  • Locked
  • Question
Outlook VBA - Trust access
Replies
3
Views
937
hermanlaksko
hermanlaksko
remeng
  • Locked
  • Question
Getting results from a dynamic dimension array
Replies
2
Views
631
Andrzejek
Andrzejek
SLW89
  • Locked
  • Question
VBA newbie needs help
Replies
4
Views
542
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top