asked on

Help with module in Access 2010

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

Roman F

ASKER

Please keep in mind that I am exporting the file into excel template

dlmille

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

Roman F

ASKER

Thank Dave,
i have noteced that you changed Obect to Excel.Application
and it breaks right on this line---User defined ....

dlmille

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

Change that back to Object.

Cheers,

Dave

Roman F

ASKER

In this case, i am getting the error on---> .Borders(xlEdgeLeft).LineStyle = xlContinuous

SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rory Archibald

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

Roman F

ASKER

thank you guys, really appreciated
What is the number 1 in .Borderaround 1
represent? Outside borders?
i NEED ALL BORDERS AND THICK BOX BORDER

Rory Archibald

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

Roman F

ASKER

thank you very much, works great
what is the linestyle

Rory Archibald

linestyle is xlcontinuous, weight was the equivalent of xlThick.

Roman F

ASKER

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

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dlmille

Cool on the Borders line style method rorya