Excel in VBA help

[Paulsburbon]

Hi All,

I have a long module that is exporting data from Access to an excel spread sheet. I currently have one long function. I would like to break it up to many functions. I currently pass the excel worksheet object into a second function from the first and work with the sheet then return the sheet at the end of the second function which works. I however get an error of 91 with or object not set. If I do the same thing with a third function it doesn't change anything in the sheet. What am I doing wrong? Thanks for anyone who takes the time to read this.

Paul

 

[boblarson]

What am I doing wrong?

Well, Paul -

The first thing would be not posting the code so we can tell what you're doing wrong .

 

[ajetrumpet]

Well, Paul -

The first thing would be not posting the code so we can tell what you're doing wrong .

this angry old chap also agrees. definately do not post the code. laughing....

 

[MSAccessRookie]

My Magic 8-Ball says...

You will not get the assistance that you desire until you are able to post your code so that someone here can look at it.

P. S. The Magic 8-Ball is almost never wrong! Please post the code for us to look at.

 

[ajetrumpet]

My Magic 8-Ball says...

mine never talked. you're lucky to have gotten one that did!

 

[Paulsburbon]

Here is my code:

Option Compare Database
Option Explicit

Private xlApp As Excel.Application
Private xlBook As Excel.Workbook
Private xlSheet1 As Excel.Worksheet
Private xlSheet2 As Excel.Worksheet

Public Function ViewExcel()
On Error GoTo Err_ViewExcel


Set xlApp = New Excel.Application
        With xlApp
            Set xlBook = .Workbooks.Add
            Set xlSheet1 = xlBook.Worksheets("Sheet1")
            Set xlSheet2 = xlBook.Worksheets("Sheet2")
        End With
        
        xlApp.Visible = True
        xlSheet1 = MergeCells(xlSheet1)
        xlSheet1 = ColumnWidths(xlSheet1)

Exit_ViewExcel:
    Set xlSheet1 = Nothing
    Set xlSheet2 = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing
    Exit Function

Err_ViewExcel:
    MsgBox "Error is ViewExcel, Description=" & Err.Description & ", Number=" & Err.Number & ", Source=" & Err.Source
    GoTo Exit_ViewExcel

End Function

Private Function MergeCells(xlSheet1 As Excel.Worksheet) As Excel.Worksheet
    With xlSheet1
        .Range("A2", "B2").Merge (True)
        .Range("C2", "E2").Merge (True)
        .Range("F2", "G2").Merge (True)
        .Range("K1", "L1").Merge (True)
        .Range("M2", "O2").Merge (True)
        .Range("A4", "B4").Merge (True)
        .Range("F4", "H4").Merge (True)
        .Range("K4", "L4").Merge (True)
        .Range("M4", "O4").Merge (True)
    End With
    MergeCells = xlSheet1
End Function

Private Function ColumnWidths(xlSheet1 As Excel.Worksheet) As Excel.Worksheet
    With xlSheet1
        .Columns(1).ColumnWidth = 2.86
        .Columns(2).ColumnWidth = 2.86
        .Columns(3).ColumnWidth = 21.71
        .Columns(4).ColumnWidth = 12
        .Columns(5).ColumnWidth = 6.57
        .Columns(6).ColumnWidth = 7.43
        .Columns(7).ColumnWidth = 5.86
        .Columns(8).ColumnWidth = 10.14
        .Columns(9).ColumnWidth = 9
        .Columns(10).ColumnWidth = 9
        .Columns(11).ColumnWidth = 4.29
        .Columns(12).ColumnWidth = 7.14
        .Columns(13).ColumnWidth = 9
        .Columns(14).ColumnWidth = 9
    End With
    ColumnWidths = xlSheet1
End Function

 

[Paulsburbon]

sorry I though I posted it.

 

[boblarson]

Paul:

Good start. Not trying to nitpick or anything but we do need the full stuff, including the declarations. And, also please use the code tags here for the forum for code to show better on your posts.

 

[Paulsburbon]

Ok no problem with the fixing of my post it has been a while since I posted on here

 

[boblarson]

Okay, I think the parts in red need to be done:

Private Function MergeCells(xlSheet1 As Excel.Worksheet) [COLOR="red"]As Excel.Worksheet ' <-REMOVE THIS[/COLOR]
    With xlSheet1
        .Range("A2", "B2").Merge (True)
        .Range("C2", "E2").Merge (True)
        .Range("F2", "G2").Merge (True)
        .Range("K1", "L1").Merge (True)
        .Range("M2", "O2").Merge (True)
        .Range("A4", "B4").Merge (True)
        .Range("F4", "H4").Merge (True)
        .Range("K4", "L4").Merge (True)
        .Range("M4", "O4").Merge (True)
    End With
    [COLOR="red"]MergeCells = xlSheet1 '<---REMOVE THIS[/COLOR]
End Function

Private Function ColumnWidths(xlSheet1 As Excel.Worksheet) [COLOR="red"]As Excel.Worksheet '<--REMOVE THIS[/COLOR]
    With xlSheet1
        .Columns(1).ColumnWidth = 2.86
        .Columns(2).ColumnWidth = 2.86
        .Columns(3).ColumnWidth = 21.71
        .Columns(4).ColumnWidth = 12
        .Columns(5).ColumnWidth = 6.57
        .Columns(6).ColumnWidth = 7.43
        .Columns(7).ColumnWidth = 5.86
        .Columns(8).ColumnWidth = 10.14
        .Columns(9).ColumnWidth = 9
        .Columns(10).ColumnWidth = 9
        .Columns(11).ColumnWidth = 4.29
        .Columns(12).ColumnWidth = 7.14
        .Columns(13).ColumnWidth = 9
        .Columns(14).ColumnWidth = 9
    End With
    [COLOR="Red"]ColumnWidths = xlSheet1 '<----REMOVE THIS[/COLOR]
End Function

 

[Paulsburbon]

ok got different error. Object doesn't support this method or property.

 

[boblarson]

ok got different error. Object doesn't support this method or property.

What line does it highlight?

 

[gemma-the-husky]

i find it hard to know the correct syntax/object model with excel, and its always trial and error, and trying to find stuff others have done. it doesnt seem as well documented as access.

which bit is going wrong?

i suspect it will be something like the

columns or range collection not being a property of the worksheet -maybe they are properties a cells object, or something like that

 

[Paulsburbon]

The first method goes thru ok. It does raise an error but completes it's job. The second method to change the size of the columns does not run. I guess I can stop error trapping and let it pass thru. But I would like to find out what is wrong. The first line of the method to merge cells throws the error and if I put it all on one method it works fine no error. I would like this split up though.

 

[DCrake]

Seems to me that the OP has missed the boat somewhat. From what I see he is trying to create some sort of template for the data to be posted to. For example setting col widths, merging cells, etc. Would it not be better to create this layout in Excel as a template and when needed use filecopy to make a copy of the file and open that. This would cut out alot of code not to say the time and effort needed to resolve the current issue.

Ok it might be deemed as being a learning exercise but htat can be done at your leasure.

David

 

[boblarson]

The first method goes thru ok. It does raise an error but completes it's job. The second method to change the size of the columns does not run. I guess I can stop error trapping and let it pass thru. But I would like to find out what is wrong. The first line of the method to merge cells throws the error and if I put it all on one method it works fine no error. I would like this split up though.

What error does it raise? You say it does on the first line of the merge code?

 

[Paulsburbon]

David- Thanks for your reply. What should I google to create an excel template and export the data to the template? Do you have a quick link. I could get it to do it as a single function but did want to learn why my code was not working. I thought because I had no programmed in vba for some time now that I was missing something.

Bob- The first merge line sets off the error. I use a msgbox before and after the merge line to see when the error fires. The error number is 91 with or object not set.

 

[boblarson]

I believe you also need to call the functions differently. Instead of this:
xlSheet1 = MergeCells(xlSheet1)
xlSheet1 = ColumnWidths(xlSheet1)

use

MergeCells xlSheet1
ColumnWidths xlSheet1

 

[Paulsburbon]

Thanks bob but same error happened. I've never read that a method could be called that way. Weird that they allow different ways to call them. Do you have another ideas? "Variable or with block not set" is the actual description.

 

[boblarson]

Thanks bob but same error happened. I've never read that a method could be called that way. Weird that they allow different ways to call them. Do you have another ideas? "Variable or with block not set" is the actual description.

Is this on a spreadsheet? Or in Access? Can you post the file here so we can play with it?