We help IT Professionals succeed at work.

Help with module in Access 2010

Roman F
Roman F used Ask the Experts™
on
i have a working module. It does the following:
run the query and export the results into excel file. No problem here at all.
What i need your help:
the cursor of the file should be in A1 cell
the width of the all columns should be auto width
it should be surrounded by border
Please help

Option Compare Database
Option Explicit

Sub ExportToTemplate()
Dim xlObj As Object, xltPath As String
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("myQuery")  ' here is the query name

xltPath = CurrentProject.Path & "\Output.xltx"  ' please have a template ready always

Set xlObj = CreateObject("Excel.Application")
    xlObj.Workbooks.Add (xltPath)
    With xlObj
        .Range("A2").CopyFromRecordset rs
    End With


    xlObj.ActiveWorkbook.saveas CurrentProject.Path & "\Output " & Format(Date, "mm-dd-yy") & ".xlsx"

xlObj.Quit
Set xlObj = Nothing


MsgBox "Completed!"
End
End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Please keep in mind that I am exporting the file into excel template
Most Valuable Expert 2012
Top Expert 2012

Commented:
Try this:

Sub ExportToTemplate()
Dim xlObj As Excel.Application, xltPath As String
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("myQuery")  ' here is the query name

xltPath = CurrentProject.Path & "\Output.xltx"  ' please have a template ready always

    Set xlObj = CreateObject("Excel.Application")
    xlObj.Workbooks.Add (xltPath)
    With xlObj
        .Range("A2").CopyFromRecordset rs
        With .Range("A2").Resize(rs.RecordCount, rs.Fields.Count)
        
            'fit all columns being returned from recordset
            .EntireColumn.AutoFit
            
            'do border around result set
            .Borders(xlEdgeLeft).LineStyle = xlContinuous
            .Borders(xlEdgeTop).LineStyle = xlContinuous
            .Borders(xlEdgeBottom).LineStyle = xlContinuous
            .Borders(xlEdgeRight).LineStyle = xlContinuous
        
            'put cursor in cell A1
            .Range("A1").Select
    End With


    xlObj.ActiveWorkbook.SaveAs CurrentProject.Path & "\Output " & Format(Date, "mm-dd-yy") & ".xlsx"

    xlObj.Quit
    Set xlObj = Nothing


    MsgBox "Completed!"

End Sub

Open in new window


Dave

Author

Commented:
Thank Dave,
i have noteced that you changed Obect to Excel.Application
 and it breaks right on this line---User defined ....
Most Valuable Expert 2012
Top Expert 2012

Commented:
My Apologies - I was lazy converted to Excel.Application for the Intellisense.

Change that back to Object.

Cheers,

Dave

Author

Commented:
In this case, i am getting the error on---> .Borders(xlEdgeLeft).LineStyle = xlContinuous
Most Valuable Expert 2012
Top Expert 2012
Commented:
Another late binding fopah!  xlContinuous is an Excel object constant and its value should be 1.  And then there's the xlEdge enumerations that need values as well - coded below with documentation.

Here's the code once more with corrections

Sub ExportToTemplate()
Dim xlObj As Object, xltPath As String
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("myQuery")  ' here is the query name

xltPath = CurrentProject.Path & "\Output.xltx"  ' please have a template ready always

    Set xlObj = CreateObject("Excel.Application")
    xlObj.Workbooks.Add (xltPath)
    With xlObj
        .Range("A2").CopyFromRecordset rs
        With .Range("A2").Resize(rs.RecordCount, rs.Fields.Count)
        
            'fit all columns being returned from recordset
            .EntireColumn.AutoFit
            
            'do border around result set
            .Borders(7).LineStyle = 1 'xlEdgeLeft, xlContinuous
            .Borders(8).LineStyle = 1 'xlEdgeTop, xlContinuous
            .Borders(9).LineStyle = 1 'xlEdgeBottom, xlContinuous
            .Borders(10).LineStyle = 1 'xlEdgeRight, xlContinuous
        
            'put cursor in cell A1
            .Range("A1").Select
    End With


    xlObj.ActiveWorkbook.SaveAs CurrentProject.Path & "\Output " & Format(Date, "mm-dd-yy") & ".xlsx"

    xlObj.Quit
    Set xlObj = Nothing


    MsgBox "Completed!"

End Sub

Open in new window


Dave
Most Valuable Expert 2011
Top Expert 2011

Commented:
NFP:

I'd replace this:
            .Borders(7).LineStyle = 1 'xlEdgeLeft, xlContinuous
            .Borders(8).LineStyle = 1 'xlEdgeTop, xlContinuous
            .Borders(9).LineStyle = 1 'xlEdgeBottom, xlContinuous
            .Borders(10).LineStyle = 1 'xlEdgeRight, xlContinuous

Open in new window


with
.Borderaround 1

Open in new window

Author

Commented:
thank you guys, really appreciated
What is the number 1 in .Borderaround 1
represent?   Outside borders?
i NEED ALL BORDERS AND THICK BOX BORDER
Most Valuable Expert 2011
Top Expert 2011

Commented:
1 means a continuous border. if you want internal borders too, then use:
With .Borders
   .weight = 4
   .linestyle = 1
end with

Open in new window

Author

Commented:
thank you very much, works great
what is the linestyle
Most Valuable Expert 2011
Top Expert 2011

Commented:
linestyle is xlcontinuous, weight was the equivalent of xlThick.

Author

Commented:
may be i am asking too much, but i need the
all borders inside and thick outside, so far everything is thick -inside and outside
Most Valuable Expert 2011
Top Expert 2011
Commented:
then
.borders.linestyle = 1
.borderaround 1, 4

Open in new window

Most Valuable Expert 2012
Top Expert 2012

Commented:
Cool on the Borders line style method rorya