Object required error on For Each c In Selection 2

[Prattdakota]

I'm having an issue while trying to edit an Excel workbook from Access using VBA. The odd thing is, when I open the database and run the routine the first time it works. When I try to run it a second time I get a "run time error 424 Object required" message. When I go to debug, the program has stopped on the "For Each c In Selection" line of code below. I assumed that I was not cleaning something up correctly since it worked the first time but not subsequent times. Setting the objects to "nothing" does not seem to help.

After I run the routine the first time Excel does save and close itself properly and so is not open when I run the routine the second time.

Any assistance would be appreciated.

Sub FormatExport(strFileName As String)
    
    Dim objExcelApp As Excel.Application
    Dim objWorkbook As Excel.Workbook
    Dim objWorksheet As Excel.Worksheet

    Set objExcelApp = New Excel.Application
    With objExcelApp
        .Visible = True
        Set objWorkbook = .Workbooks.Open(DBPath & "Template", , False)
    End With

    Set objWorksheet = objWorkbook.Sheets(1)
    objWorksheet.Activate

    objWorksheet.Rows("1:1").Select
    objExcelApp.Selection.Delete
    objWorksheet.Range("B5:M100").Select
    
    For Each c In Selection
        c.Value = c.Value
    Next c
    Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
    
    objWorkbook.Close True, strFileName
    objExcelApp.Quit
    
    Set objExcelApp = Nothing
    Set objWorkbook = Nothing
    Set objWorksheet = Nothing
    
End Sub

 

[TheAceMan1]

How are ya Prattdakota . . .

[blue]    Dim objExcelApp As Excel.Application
    Dim objWorkbook As Excel.Workbook
    Dim objWorksheet As Excel.Worksheet
    [purple][b]Dim c AS Variant[/b][/purple][/blue]

See Ya! . . . . . .

 

[Prattdakota]

Hi AceMan,

Thanks very much for the response. I've given your suggestion a try and continue to get the same error. Seems strange. I guess I'll write the code some other way to try to get it to work.

Thanks again.

 

[Golom]

You have unqualified references. Try this

For Each c In [red]objWorksheet.[/red]Selection
   c.Value = c.Value
Next c
[red]objWorksheet.[/red]Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"

 

[TheAceMan1]

Prattdakota . . .

I was in a hurry with my last post.

I'm surprised you don't get error on first run of the code. In . . .

[blue]For Each c In [purple][b]Selection[/b][/purple][/blue]

. . . access has no way of knowing what object the [purple]Selection[/purple] Collection belongs to! I believe it should be:

[blue]   For Each c In [purple][b]objWorksheet.[/b][/purple]Selection[/blue]

The same problem occurs in:

[blue]   [purple][b]Selection[/b][/purple].NumberFormat = . . .[/blue]

A [blue]With/End With[/blue] would work great here.

If you perform a compile it should be revealing.

Also it may be better if you explain what your trying to do . . .

See Ya! . . . . . .

 

[Prattdakota]

Golom and AceMan. Thanks to both of you very much for pointing me in the right direction. You are both correct in that I had an unqualified reference. I kept thinking it was on the "c" instead of on the .Selection. ObjWorksheet.Selection seemed like the intuitive answer to me also, however, Excel really seems to like you to refer to the application object instead of the workbook or worksheet object. So, when I used the following code, Bingo, it worked like a charm.

    For Each c In objExcelApp.Selection
        c.Value = c.Value
    Next c
    objExcelApp.Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"

Also, if you're curious AceMan, what I am doing is building a report in Access which I then export to Excel. This routine performs some formatting in Excel for me. I know, why send to Excel? Let it suffice to say I'm working with a bunch of accountants. We'll get there I just gotta bring them along slowly.