Simple Excel/Access VBA question - quite urgent though... 1

[stickers]

Hi All,

I'm sure this is quite simple but I can't see where I'm going wrong. I'm exporting an Access query to Excel, but formatting the Excel sheet afterwards. Here's the main bit of code - I've numbered the lines so I can explain the problem!

(1)
xlProjectCosts.ActiveSheet.Cells(currentRow, 2).CopyFromRecordset rsMatrix
(2)
currentCol = rsMatrix.Fields.Count + 2
currentRow = currentRow + rsMatrix.RecordCount
(3)
'xlProjectCosts.ActiveSheet.Range(Cells(7, 3), Cells(currentRow, currentCol)).NumberFormat = "$#,##0.00"

I need all the variables in line(3) because the recordset will always have different numbers of columns and rows. I want all of the cells to be formatted as currency. Previously I had specified a range to format using the A1 notation, and then the whole thing ran fine. If I comment out line(3) it still works fine. But when I run it as it is, I get an error in line(1): 1004 Application-defined or object-defined error.

Can anyone throw any light on this?

Thanks in advance!

 

[Stretchwickster]

You have an open single quote on line 3 - is that a typo whilst you were entering the tek-tips post or is that in the original code?

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[xlbo]

Well a 1004 error usually means you are trying to refer to something that isn't there

Only thing I would suggest is that you use
currentRow = xlProjectCosts.ActiveSheet.range("C65536&quot.end(xlup).row

being as you are entering the recordset into col C

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[stickers]

Yes, sorry I'd commented out that line whilst trying to figure out the problem. With the line commented out the rest of it seems to work fine (although it doesn't get formatted, obviously) But when I leave it in, the error pops up from line (1). Thanks for replying - any ideas?

Cheers!!!

 

[stickers]

Geoff, I must have been replying to Clive whilst you were writing.

I tried again with your code and got the same error number but a different description: Method 'Cells' of object '_Global' failed.

I am puzzled.

 

[xlbo]

Stickers - are there any lines of code BETWEEN the 3 you posted ??
The line of code (3) works fine without "xlprojectcosts"
what is xlprojectcosts set to ?? workbook, worksheet or application ??

can you provide more of the code prior to the error ?

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[stickers]

Yes - I've just been trying it a bit more and it's really messing around properly now!

The code below is in a separate module. The sub is called from the event handler of a command button on an Access form. As I say - until I tried to mess around with the range to be formatted it seemed to work fine. I have commented out my error handlers because I wanted the default one to come up so that I'd find out where the problems were. I've tried stepping through the code, but the strange thing is that when there's an error it doesn't go to the error handler. Bizarre.


Option Compare Database
Option Explicit
Dim xlProjectCosts As Excel.Application

Public Sub CreateLifeCycleReport(ProjectName As String, QueryName As String)


Dim cnCurrent As ADODB.Connection
Dim rsMatrix As ADODB.Recordset
Dim currentRow As String
Dim currentCol As String
Dim i As Integer

'On Error GoTo ErrHandler

Set cnCurrent = CurrentProject.Connection
Set rsMatrix = New ADODB.Recordset

rsMatrix.Open "SELECT * FROM " & QueryName, cnCurrent, adOpenDynamic, adLockPessimistic

Set xlProjectCosts = New Excel.Application
xlProjectCosts.Visible = True
xlProjectCosts.Workbooks.Add
'xlProjectCosts.ActiveWindow.DisplayGridlines = False

'Enter details of project, autofit cells and put into bold type
xlProjectCosts.ActiveSheet.Cells(1, 1).Value = "Prepared:"
xlProjectCosts.ActiveSheet.Cells(1, 2).Value = Now()
xlProjectCosts.ActiveSheet.Columns(2).AutoFit
xlProjectCosts.ActiveSheet.Cells(3, 1).Value = "Project:"
xlProjectCosts.ActiveSheet.Cells(3, 2).Value = ProjectName
xlProjectCosts.ActiveSheet.Columns(2).AutoFit

xlProjectCosts.ActiveSheet.Range("A1:B3&quot.Font.Bold = True

currentRow = 5

'Enter Field names into row 5 (this is a crosstab query)

For i = 1 To rsMatrix.Fields.Count
xlProjectCosts.ActiveSheet.Cells(currentRow, i + 2).Value = rsMatrix.Fields(i).Name
xlProjectCosts.ActiveSheet.Cells(currentRow, i + 2).Font.Bold = True
xlProjectCosts.ActiveSheet.Columns(i + 2).ColumnWidth = 11.14
Next

'I want a bit of space between headings and the rest of the data so set current row to 7
currentRow = 7
currentCol = rsMatrix.Fields.Count + 2

'copy data from recordset - the queries I'm putting in all work fine

xlProjectCosts.ActiveSheet.Cells(currentRow, 2).CopyFromRecordset rsMatrix
'currentRow + rsMatrix.RecordCount

'the first column has text in so I'd like to leave it out of the formatting
currentRow = xlProjectCosts.ActiveSheet.Range("C65536&quot.End(xlUp).Row

'select the range with numbers in and format to currency
'xlProjectCosts.ActiveSheet.Range(Cells(7, 3), Cells(currentRow, currentCol)).NumberFormat = "$#,##0.00"

xlProjectCosts.ActiveSheet.Columns(3).AutoFit

Exit Sub

'ErrHandler:

'If Err.Number = 3265 Then
'Resume Next
'Else
'MsgBox Err.Number & Err.Description, , "Jarvis FFE Database"
'Exit Sub
'End If

End Sub

 

[xlbo]

Right - fine - the problem is that xlprojectcosts is defined as application - worksheets are a property of the workBOOK
add a line after:
xlProjectCosts.Workbooks.Add
like
dim mWB as workbook
set mWB = activeworkbook

then use
With xlprojectcosts.mWB
.ActiveSheet.Cells(1, 1).Value = "Prepared:"
.ActiveSheet.Cells(1, 1).Value = "Prepared:"
.ActiveSheet.Cells(1, 2).Value = Now()
.ActiveSheet.Columns(2).AutoFit
.ActiveSheet.Cells(3, 1).Value = "Project:"
.ActiveSheet.Cells(3, 2).Value = ProjectName
.ActiveSheet.Columns(2).AutoFit

.ActiveSheet.Range("A1:B3&quot.Font.Bold = True

etc etc
end with






Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[stickers]

Right, I've just tried this (and where I haven't got a with statement I've inserted .mwb. into my code), and on this line

With xlProjectcosts.mwb

I get an error saying: object does not support this method

Do you have any more ideas?

Thank you so much for your help - I've not been using VBA very long and although I've learned a lot, I still get stuck quite frequently!! Hope I can return the favour sometime.

 

[xlbo]

Have you dimmed and set mWB ??

This should work:

Option Compare Database
Option Explicit

Public Sub CreateLifeCycleReport(ProjectName As String, QueryName As String)


Dim cnCurrent As ADODB.Connection
Dim rsMatrix As ADODB.Recordset
Dim currentRow As long
Dim currentCol As integer
Dim i As Integer
Dim xlProjectCosts As Excel.Application
Dim mWB as Excel.workbook
'On Error GoTo ErrHandler

Set cnCurrent = CurrentProject.Connection
Set rsMatrix = New ADODB.Recordset

rsMatrix.Open "SELECT * FROM " & QueryName, cnCurrent, adOpenDynamic, adLockPessimistic

Set xlProjectCosts = New Excel.Application
xlProjectCosts.Visible = True
xlProjectCosts.Workbooks.Add
'xlProjectCosts.ActiveWindow.DisplayGridlines = False
set mWB = activeworkbook
'Enter details of project, autofit cells and put into bold type
With xlprojectcosts
with .mWB
.ActiveSheet.Cells(1, 1).Value = "Prepared:"
.ActiveSheet.Cells(1, 2).Value = Now()
.ActiveSheet.Columns(2).AutoFit
.ActiveSheet.Cells(3, 1).Value = "Project:"
.ActiveSheet.Cells(3, 2).Value = ProjectName
.ActiveSheet.Columns(2).AutoFit

.ActiveSheet.Range("A1:B3&quot.Font.Bold = True

currentRow = 5

'Enter Field names into row 5 (this is a crosstab query)

For i = 1 To rsMatrix.Fields.Count
.ActiveSheet.Cells(currentRow, i + 2).Value = rsMatrix.Fields(i).Name
.ActiveSheet.Cells(currentRow, i + 2).Font.Bold = True
.ActiveSheet.Columns(i + 2).ColumnWidth = 11.14
Next

'I want a bit of space between headings and the rest of the data so set current row to 7
currentRow = 7
currentCol = rsMatrix.Fields.Count + 2

'copy data from recordset - the queries I'm putting in all work fine

.ActiveSheet.Cells(currentRow, 2).CopyFromRecordset rsMatrix

'the first column has text in so I'd like to leave it out of the formatting
currentRow = .ActiveSheet.Range("C65536&quot.End(xlUp).Row

'select the range with numbers in and format to currency
.ActiveSheet.Range(Cells(7, 3), Cells(currentRow, currentCol)).NumberFormat = "$#,##0.00"

.ActiveSheet.Columns(3).AutoFit
end with
end with
Exit Sub

'ErrHandler:

'If Err.Number = 3265 Then
'Resume Next
'Else
'MsgBox Err.Number & Err.Description, , "Jarvis FFE Database"
'Exit Sub
'End If

End Sub

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Stretchwickster]

Apologies for my "dumb" post, I was still thinking in Delphi mode, forgetting that a single quote marks a comment!!

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[stickers]

Hi Geoff,

I pasted your code straight in, and it doesn't like the line

with .mWB

Before you posted back I tried substituting using ActiveWorkbook instead of mWB, which worked OK, but then I still get the same problem with the cells and ranges that I started off with.

I think I have all the requisite libraries ticked - are there any obvious ones that I might be missing?

 

[xlbo]

I do apologise - you don't need the xlprojectcosts at all apart from to set up the new workbook

change
With xlprojectcosts
with .mWB

to just
with mWB

and remove one of the end withs from the end - should work just fine

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[stickers]

Funny - I tried that and it doesn't like it! But I adapted your code - took out all references to mWB and changed that line to

With .ActiveWorkbook

and suddenly it all works perfectly!! So here are two stars for being great!

Like your Wilde quote by the way!

Stretchwickster (bit of a mouthful that) - no problems - have lost count of the number of times I have posted something and twigged 5 minutes later....

 

[xlbo]

Bizarre - worked fine for me with mWB but hey - if it works, it works
coolio

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[stickers]

Geoff,

Sorry to be a pain - just one last question:

This works fine once. However, if I try running it again, I get the same problem with error 1004. The only solution seems to be to shut the db down, or compact it.

Thank you so much, again!

Sarah

 

[xlbo]

Try setting all your variables to nothing eg
set xlprojectcosts = nothing
set cnCurrent =nothing
set rsMatrix = nothing




Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[stickers]

Nope... Thanks anyway... I think I'm going to give up for today and go home!!!

thanks for all your help.