Problem running macro more than once without re starting program 1

[chrisgarvey]

Hello.

I have a vb program that creates several reports by opening an excel file inserting the contence of a database and then sorting as required using an excel macro.

There are three difference macro's depending on the report required.

The program works if I only generate 1 report however if I try and run more than 1 macro from VB without closing and reopening the program the following error message apprears:

Run-time error '1004' Method 'run' of object' _application' failed. - this appears on the line of code in vb that runs the macro.

Any thoughts would be much appreciated...

Chris.

 

[johnwm]

You may need to show us a bit of code! faq222-2244 paragraph 14 gives guidelines on this

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[chrisgarvey]

Hopefully my code should appear below,

The problem occours when the macro is run for the second time:

Dim n As Long 'declare n as a long integer, this will be used to store the number of records in db
Dim a, b As String 'declare as string

datDiningClub.Recordset.MoveFirst 'move to the first record of the db

m = datDiningClub.Recordset.RecordCount 'let the variable m to equal the number of records in the db

Set x = CreateObject("Excel.application") 'create an excel application, with variable name of x

With x
.Workbooks.Open FileName:=App.Path & ("\reports") 'open the existing templet report
.Sheets("data").Activate 'open the excel sheet 'data'

    n = 2 'to allow for 1 row to enter column headings
    a = "A" & n 'calculating on which row to place the data
    b = "B" & n
    c = "C" & n
    d = "D" & n
    e = "E" & n
    f = "F" & n
    g = "G" & n
    h = "H" & n
    i = "I" & n
    j = "J" & n

    .Range("A2").Value = datDiningClub.Recordset(0)
    .Range("B2").Value = datDiningClub.Recordset(1)
    .Range("C2").Value = datDiningClub.Recordset(2)
    .Range("D2").Value = datDiningClub.Recordset(3)
    .Range("E2").Value = datDiningClub.Recordset(4)
    .Range("F2").Value = datDiningClub.Recordset(5)
    .Range("G2").Value = datDiningClub.Recordset(6)
    .Range("H2").Value = datDiningClub.Recordset(7)
    .Range("I2").Value = datDiningClub.Recordset(8)
    .Range("J2").Value = datDiningClub.Recordset(9)

 Do Until datDiningClub.Recordset.EOF
 
     .Range(a).Value = datDiningClub.Recordset(0)
     .Range(b).Value = datDiningClub.Recordset(1)
     .Range(c).Value = datDiningClub.Recordset(2)
     .Range(d).Value = datDiningClub.Recordset(3)
     .Range(e).Value = datDiningClub.Recordset(4)
     .Range(f).Value = datDiningClub.Recordset(5)
     .Range(g).Value = datDiningClub.Recordset(6)
     .Range(h).Value = datDiningClub.Recordset(7)
     .Range(i).Value = datDiningClub.Recordset(8)
     .Range(j).Value = datDiningClub.Recordset(9)
     .Range(a).Select
     
     .Selection.EntireRow.Insert 'now insert the row

     n = n + 1 'add 1 to n, so that the next entry is not inserted over the top

     datDiningClub.Recordset.MoveNext 'move to the next record in the db

 Loop

'case statement to select the correct macro to run
Select Case SearchType
  Case "SortMembershipNumber"
   'Run the macro to perform sort of data
       excel.Application.Run "SortMembershipNumber"
  Case "SortSurnameAZ"
       excel.Application.Run "SortSurnameAZ"
  Case "SortSurnameZA"
       excel.Application.Run "SortSurnameZA"
  End Select

.Visible = True 'show the spreadsheet on the screen

Application.Quit
Set objexcel = Nothing 'destroy the excel object
SearchType = "" 'clear the variable for next time
Set x = Nothing 'destroy the value of x for next time

End Sub

Still struggling to find a solution.....

Any guidance would be appreciated.

Chris.

 

[johnwm]

You appear to have 3 different references to the Excel application object;
[tt]
Set x = CreateObject("Excel.application")
[/tt]
which you set up and destroy in this Sub, and
[tt]
excel.Application.Run "SortMembershipNumber"
[/tt]
which you use without specific setup or destruction, and finally
[tt]
Set objexcel = Nothing 'destroy the excel object
[/tt]

which you destroy without setting up within this sub. You probably need to check this through first.

I also notice in declarations: [tt]
Dim a, b As String 'declare as string
[/tt]
which declares a as Variant and b as String. You also use a load of other variables which are not (visibly) declared. That may indicate that you don't have Option Explicit set at the start of the module's declaration section.

I also notice that you set values to the String variables a, b, c etc outside the loop which increments n, so the values don't change with the loop counter.

Try sorting those bits, and come back if there is anything else.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[chrisgarvey]

Very Valid Points johnwm, thank you.

I am trying to correct them, but struggling.....

I now understand I need to destroy the object

'excel.Application.run "sortMembershipNumber"

I cannot work out how to do this?

I have tried:

excel.application=nothing
excel.application.hide
excel.application.close

to name a few.....

Any suggestions?

Thanks once again for your help so far.

Chris.

 

[johnwm]

To start and end an Excel app I would use:
[tt]

Set x = CreateObject("Excel.Application")
x.Workbooks.Open (App.Path & "\calcs.xls")
x.Visible = True
[/tt]

and to kill it:
[tt]
Dim W As Workbook
For Each W In x.Workbooks
W.Close savechanges:=False
Next W
x.Quit
Set x = Nothing
[/tt]

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[chrisgarvey]

Thanks for your help Johnmw. I think you deserve a star for that!

However, I am still having problems,

If I try and click the button containing the code that run's the macro for a second time whilst not closing down the program, i get the following message:

'Run-time error'1004':
Method 'Run' of object '_Application'failed'

Is the macro still running then??

 

[johnwm]

Are you sure which versions of the Excel app are actually running, and which one you are killing?

Try bringing up task manager and put some Breakpoints in your code so you can see if Excel is open or closed, and which app is activated and when it opens and closes.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'