Exporting a report to Excel and formatting it with Access VBA

[ghallan]

I am creating a form that selects data based on a user selected model type, flange type, and a range of line sizes. This hasn't been a problem, I am able to get the correct report from the form thus far.

My issue is the button I am using to export the report to excel. I want the button to save the file with a filename based on the parameters selected in the form, then open the excel file, delete empty columns, move the table a bit (just so the borders are visible), and then add the lines to make it look like a nice table. I am at the point where this works most of the time when I click the button. It seems to be not working properly whenever excel is already open, especially if there is an excel file that is already open which was created by this button. What really gets me is that it'll give a "Method 'Range' of object '_Global' " error sometimes but if I close the excel file and try again it will work no problem.

Keep in mind that I am a complete novice when it comes to Access and VBA in general - everything you see here I have taught myself in the last week. So feel free to treat me like a child, I am learning each step of the way.

I have omitted all the code concerning the option lists on the form. This is just the button.

Option Compare Database
Option Explicit
 
Private objExcel As Excel.Application
Private xlwb As Excel.Workbook
Private xlws As Excel.Worksheet
 
Private Sub CreateTable_Click()
 
Dim filename As String
Dim lastrow As Long
Dim lastcolumn As Long
Dim i As Long
Dim flange As String
 
Select Case FlangeType.Value
Case 1
flange = "SO"
Case 2
flange = "WN"
Case 3
flange = "WA"
End Select
 
'set filename based on filter of table and save as excel sheet
filename = "Type1drawing_" & DrawingType.Value & "_" & flange & "flange_" & minsize.Value & "_to_" & maxsize.Value & ".xls"
    DoCmd.OutputTo acOutputReport, "rptType1Drawing", "Excel97-Excel2003Workbook(*.xls)", filename, False, "", 0, acExportQualityPrint
 
'set an excel object and open the saved file to format correctly
  Set objExcel = New Excel.Application
  objExcel.Visible = True
  Set xlwb = objExcel.Workbooks.Open(filename)
  Set xlws = objExcel.Worksheets("Type 1 Drawing Maker")
 
  objExcel.ScreenUpdating = False
  objExcel.Calculation = xlCalculationManual
  xlws.Activate
 
With xlws
lastrow = Range("A1").End(xlDown).Row
lastcolumn = Range("A1").End(xlToRight).Column
i = lastcolumn
 
    'delete empty columns (non-available liners)
    Do Until i = 0
    If WorksheetFunction.CountA(Range(Cells(2, i), Cells(lastrow, i))) = 0 Then 'use row 2 so the headers aren't counted
    Columns(i).Delete
    End If
    i = i - 1
    Loop
    'move for better view and then format
    Range("A1", Cells(lastrow, lastcolumn)).Cut
    Range("b2").Select
    ActiveSheet.Paste
 
 
 'update the last column and row
lastcolumn = Range("b2").End(xlToRight).Column
lastrow = Range("b2").End(xlDown).Row
 
    Range("b2", Cells(lastrow, lastcolumn)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection
        .RowHeight = 14
        .VerticalAlignment = xlCenter
    End With
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    Columns.AutoFit
    Cells(1, 1).Select
    End With
 
 
objExcel.ScreenUpdating = True
objExcel.Calculation = xlCalculationAutomatic
xlwb.Save
Set objExcel = Nothing
Set xlwb = Nothing
Set xlws = Nothing
 
End Sub
 

[Rabbit]

You could create a "template" workbook with all your formatting already done. Then make a copy of the workbook and import the data into it. That way you don't have to code all the formatting work and makes it easier to change formatting later down the road.

[ghallan]

The issue is that I will not be the only perosn accessing this database. So the code needs to produce the Excel sheet in the correct format from any computer that has Excel and Access on it. So the Access VBA needs to complete the entire task if I'm not mistaken.

[Rabbit]

Can you not put the excel template in a central network location that everyone has access to? Or include the excel template with each person's copy of the Access database?

[ghallan]

I don't know, I'm just a summer intern. I suppose that is probably possible but shouldn't it be possible to get Access VBA to control Excel anyways?

[Rabbit]

It's possible, it's just way more complicated. You can refer to this article if you want to go that route: http://bytes.com/topic/access/insigh...ion-automation

[NeoPa]

I just deleted your question in the article thread. It's a perfectly valid question - but article threads aren't there for asking questions in.

If you repeat it here I will see it and can respond. Alternatively, you can post it in a new thread if you prefer. I'm more likely to see it from in here though ;-)

Strangely, one of my first ever tasks myself in Access was to produce a procedure called "Prettify()" which basically did a similar job to what you're asking about. That was a few years ago now mind.

[ghallan]

Well the question basically pertained to the one this thread is about. I don't quite understand what part of this code is not properly referencing excel.

[MikeTheBike]

Hi

When automaton Excel (or any office application) in Access etc you need to explicitly refer to the Excel object because, If you do not, then the application running he code makes assumption as ti what you are referring to, which is not good.

I am a bit surprised this work at all, but i have had a similar experience with Word in my 'younger' days.

In your code you do not qualify any range or cell object so access will not now you have opened a new file and assume it is the original file (I think). There is a certain amount of supposition in these comments. However if you use explicit references then there is no ambiguity and the program knows exactly what you indended.

So, below I have moded a part of you code to indicate how I would do this

With xlws
    lastrow = .Range("A1").End(xlDown).Row
    lastcolumn = .Range("A1").End(xlToRight).Column
    i = lastcolumn
 
    'delete empty columns (non-available liners)
    Do Until i = 0
        If objExcel.WorksheetFunction.CountA(.Range(.Cells(2, i), .Cells(lastrow, i))) = 0 Then 'use row 2 so the headers aren't counted
        .Columns(i).Delete
    End If
    i = i - 1
    Loop
    'move for better view and then format
    .Range("A1", xlws.Cells(lastrow, lastcolumn)).Cut
    .Range("b2").Select
    .Paste
 
 
    'update the last column and row
    lastcolumn = .Range("b2").End(xlToRight).Column
    lastrow = .Range("b2").End(xlDown).Row
 
    'FORMAT RANGE
    With .Range("b2", .Cells(lastrow, lastcolumn))
        .RowHeight = 14
        .VerticalAlignment = xlCenter
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With .Font
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
        End With
    End With
 
    .Columns.AutoFit
    .Cells(1, 1).Select
End With
 

In short, in this code block, every time you use the Range or Cell obects you need a period/full stop in front of it.
You will note I have remove all reference to the 'With Selection' and used 'With .Range("b2", .Cells(lastrow, lastcolumn))' directly as you do not deed to select the range to refer to it.

Note, I also think the 'WorksheetFunction' if a part of the application object ie. objExcel in this case.

As I have/cannot run the code I may have missed some object referrals.


HTH

[ghallan]

This seems to have fixed my problem of using the button two times in a row. Thank you, I didn't realize that putting a "." infront of everything was necessary. Live and learn!

[NeoPa]

ghallan:
I didn't realize that putting a "." infront of everything was necessary.

This is related to using With in your code. generally a good idea and very much to be recommended.

ghallan:
I don't quite understand what part of this code is not properly referencing excel.

Well, you have to do a bit of work on that yourself first. Just dumping code (114 lines) for others to scan through and find your problems without any clues isn't a great idea. None of us has the code in a working project so that would be many times more work for others than the basics would be for you.

Start by determining what is failing where in your code. Identify which reference isn't working as expected. Error messages and line numbers are important here. When you have that very basic information then ask a specific question related to that and we'll do what we can to help you.

You may no longer need the help for this question, but please remember the idea for your next ones. It will make it much easier to provide you with a quicker, and more helpful, response.

[MikeTheBike]

Hi again

Just a little more info on this subject.
I indicated my surprise that this code work at all.

It has now dawned on me that your code used early binding with a reference to the Excel library; therefore the compiler would eventually fine the object and methods after first searching the Access library.

I always use late binding (to eliminate DLL problems in different version of Office) which does not have a reference to the Excel library, in which case it would not have compiled as it was, and explicit references to the object variables would be required at all times as indicated.

The first time your code runs it assigns the unqualified objects to the first file, but this assignment is not changed in subsequently passes and is still looking for this first file, which is closed or not the active file/worksheet, I think.

So, as NeoPa said, it is always best, when using automation, to reference the objects/methods explicitly.

Hope that help.

MTB

[ghallan]

Thanks guys. This is my first time using VBA for a project and my first time using a website like this for help. All the pointers are greatly appreciated.