Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel formatting from MSAccess

Posted on 2006-06-28
5
Medium Priority
?
383 Views
Last Modified: 2008-01-09
I have a MSAccess utility that creates a XLS file and provides a bit of XLS formatting before I dump a recordset into it.

I and seeing an intermittent error in my code:

'Data Body Formatting
    With wksNew.Range("A6:R" & RecordCount)
        .Font.Size = 8
        .VerticalAlignment = xlVAlignCenter
        .BorderAround Weight:=xlThin
        .Borders(xlInsideVertical).Color = 15
        With .Borders(xlInsideHorizontal)
            .Weight = xlThin                          <----------Debug points to this line
            .Color = RGB(192, 192, 192)
        End With
    End With

Error:  Runtime Error '1004'  Unable to set the Weight Property of the Border Class.

I will get this error for a repeatable set of conditions related to the RecordSet, but NO error for a different set of conditions.  The actual data dump happens well after this command:


'Dump RecordSet to XLS

    Dim rs As DAO.Recordset
    Dim prm As DAO.Parameter
   
    Set qdf = CurrentDb.QueryDefs("DMSReport")
    'Evaluate the parameters from the form references
    For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next
    Set rs = qdf.OpenRecordset(dbOpenSnapshot)
       
    With wksNew
        .Range("A7").CopyFromRecordset rs
    End With


Help??
0
Comment
Question by:markp99
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 17000994
Is it not better to apply formatting after u done CopyFromRecordset? just a thought

see this
http://support.microsoft.com/default.aspx?scid=kb;en-us;277577
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17001250
**** Don't accept as Answer. Just curious*****
Hi Mark p
I'm wondering, if you create your code as a macro inside your spreadsheet and run it from there...is the border weight available within your workbook?

also, you might want to just line it out

.Borders(xlInsideHorizontal).Weight = xlThin                          <----------Debug points to this line
.Borders(xlInsideHorizontal).Color = RGB(192, 192, 192)
 
0
 

Author Comment

by:markp99
ID: 17001521
I found my problem, but seems strange:

There was an issue with my "RecordCount" variable (RecordCount = actualnorecords + 6).  I use this to format only the extent of the XLS sheet that will actually contain data.   In the error condition: RecordCount = 6, which is the Header of my XLS sheet which I formatted just before the problem section.

All is well now that I am getting a reliable RecordCount (there was a NULL vs "" issue in my DCount statement).
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 17893944
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question