Code Works Only after C&R (1 Viewer)

[Privateer]

I am exporting data from Access to Excel and following that up with formulas and formatting. The code is in Access and works on the Excel object and it works fine. The problem is it doesn't work a second time. I have to run compact and repair in Access and then the code works again. Below is what I am opening/creating at the beginning of the procedure and what I am closing. It fails when I try to assign a range to the CSR variable with error 1004: Method 'Cells' of object '_Global' failed

I don't have a menu with the code tag option so here it is.

Dim xl As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim CSR As Range 'Current sheet range
Dim TRN As Long 'The row number
Dim TCN As Long 'The column number

Set xl = New Excel.Application

Set xlBook = xl.Workbooks.Open(FilePath)

Set xlSheet = xlBook.Worksheets(1)

xl.Visible = True

xlSheet.Activate

TRN = xlSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
TCN = xlSheet.Cells.SpecialCells(xlCellTypeLastCell).Column

The row number is 17 and the column is 10
The code below selects range("E18:J18") and the next line puts a formula in those five cells that totals row 2 through 17, like "=SUM(E2:E17)" The cool thing is that the columns change automatically so that the formula in column J is "=SUM(J2:J17)"

Set CSR = xlSheet.Range(Cells(TRN + 1, TCN - 5), Cells(TRN + 1, TCN)) <-fails here
With CSR
.FormulaR1C1 = "=SUM(R[" & ((TRN * -1) + 1) & "]C:R[-1]C)"
...
End With

The CSR variable has been assigned many ranges up to this point, but it blows up on this specific line when the code is executed a second time.

My exit code is below. I have error trapping going on, so this runs whether there is an error or not.

CleanExit:
xl.DisplayAlerts = True
Set CSR = Nothing
xlBook.Close True
Set xlSheet = Nothing
Set xlBook = Nothing
xl.Quit
Set xl = Nothing
Exit Sub

So I think I am closing everything I am opening, but something is blowing up. I have used the watch window to follow the CSR variable and seen nothing that indicates a problem, but there is a lot being displayed. I am hoping someone else has experienced this unique feature in Access and can help me. As always, any suggestions would be much appreciated.
Privateer

 

[vbaInet]

Could you please re-format your code and enclose them in Code Tags privateer.

http://www.access-programmers.co.uk/forums/showthread.php?p=1430550#post1430550

 

[vbaInet]

It's most likely failing because you haven't qualified the reference to the Cells() object. It should be xlSheet.Cells(...)

The other thing, can you not perform these calculations in Access before exporting to Excel or are you doing it because you need the formulas to remain?

 

[Privateer]

vbaInet, I mentioned in the post that I don't have a menu with # nor do I know any other way to put code tags in a post. There is no text modification menu on my screen when I post a question.

In this example, the cells is not an object it is a method of creating a range and I had help from an Excel forum to get that one to work.

And yes, I need the formulas in the Excel spreadsheet, clients expect that.

 

[vbaInet]

Oh ok... the link I posted shows you how to type it in, you don't really need the button. Here's an example:

[code]
Dim obj as object
[/code]
... as soon as you post, those

 tags will get parsed as well. In case you're wondering why the tags above didn't get interpreted, it's because I set it not to.

With regards your problem, here's what I'm talking about:
[code]
With xlSheet
    Set CSR = [COLOR="Red"].[/COLOR]Range([COLOR="red"].[/COLOR]Cells(TRN + 1, TCN - 5), [COLOR="red"].[/COLOR]Cells(TRN + 1, TCN))
End With

Explicit referencing. Hopefully you can see the red dots.

 

[CJ_London]

I don't have a menu with the code tag option

FYFR - click on the 'Go Advanced' button, next to the 'Post Quick Reply' button

 

[vbaInet]

@CJ_London: Just tried looking up 'FYFR' and it didn't come up with any useful results. What does it mean?

 

[CJ_London]

For Your Future Reference

 

[vbaInet]

You made that up didn't you

 

[CJ_London]

YID (Yes I Did)

 

[vbaInet]

Not sure if they will catch on haha!

 

[Privateer]

vbaInet FYFR, your red dots did the trick (RDDTT), YID, thank you. I guess I am a little rusty on my Excel code. That fix closed a project and lowered my blood pressure 20 points. And to CJ_London, the go advanced flipped my post from the bottom to the top but still no menu using Win7 and IE 11.0.96.

 

[vbaInet]

Well IGID (I'm glad it did)

That's weird about the code button not showing after clicking the Advanced button. The screenshot attached is what I get. FF, IE and Chrome.

 

[CJ_London]

Don't you see this? the # button provides the code tags

ah - beaten to it!

 

[Privateer]

Afraid not. I get the emoticons across the bottom, a title box and logged in as across the top but that is it. I have snipped the screens, but I don't know how to add them to a post. I select copy in Snip, but paste is not a valid option when I right click on this message box.

I will mention that prior to replying, there is a menu above my initial post and one is called Display Modes. I tried Linear, Hybrid and Threaded, and selected Go Advanced on each, but neither produced the desired results.

There is also a posting rules box at the bottom and BB Code, Smiles, and IMG are on, but HTML is off. Is that significant? It also says I may post new, reply, edit, and attach.

I appreciate the extra effort. Let me know if you want to try anything else.

 

[vbaInet]

That's peculiar.

If you click the "Go Advanced" button, scroll down a touch and you'll see a button called "Manage Attachments", click that to bring up a dialog box that allows you to upload attachments.

 

[Privateer]

OK, so there is the screen shot.

 

[JHB]

Here is the set up:

 

[Privateer]

JHB, So that worked. Got a menu and all sorts of cool smiles. Thanks.

 

[JHB]

Good it solved your problem!