Solved

Autofitting contents of an exported query from Access to Excel

Posted on 2004-10-18
4
1,167 Views
Last Modified: 2008-03-03
Hi,
   I am trying to format an Excel workshee after exporting from Access using the TransferSPreadsheet function. The formatting required is that one of the columns needs to be fitting the entire contents (around 220-500) characters. I have tried the following code. But I get the error-User defined type not defined at the place marked by >>>>>>>>>>>>>>>>>. Secondly, what snippet of code would allow me to autofit the entire contents of the cell.

thanx

Private Sub Command71_Click()

 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
 "query1", "C:/Tryout.xls"
 
 OpenWorkbook

End Sub

Public Sub OpenWorkbook()

 Dim xl As New Excel.Application <<<<<<<<<<<<<<<<<<<<<<<<<<
 Dim wb As Excel.Workbook
 Dim ws As Excel.Worksheet

 'Show Excel.  This is optional.
 xl.Visible = True

 'Open the workbook.
 Set wb = xl.Workbooks.Open("c:\Tryout.xls")

 'Get a reference to the first worksheet.
 Set ws = wb.Worksheets(1)

 'Set the first row to bold.
 ws.Range("1:1").Font.Bold = True

 'Save the workbook.
 wb.Save

 'Close the workbook.
 wb.Close

 'Quit Excel.
 xl.Quit

 'Destroy all of the objects.
 Set ws = Nothing
 Set wb = Nothing
 Set xl = Nothing
End Sub

0
Comment
Question by:LuckyLucks
  • 2
4 Comments
 
LVL 3

Accepted Solution

by:
PoshDog earned 500 total points
ID: 12338271
Hi

Ensure you have referenced Excel in Access - In the VBA editor...  Tools... References....

To autofit the columns....

ws.range("A:Z").EntireColumn.Autofit

Posh
0
 

Author Comment

by:LuckyLucks
ID: 12341922
Hi,
    You were right about the Excel reference and right too about the Autofit, thanx PoshDog !. My mistake actually in detailing the question, maybe I should clarify the specs a bit more. My query is spitting out a table into Excel using the TransferSpreadsheet. Now autofit is making all the characters fit but this still falls off the print /print preview margins. One of the columns of the results is a memo field and its contents can range from 220- 500. Is there  a way to preformat in VBA the output to Excel such that all the cell contents fit in a way that doesnt fall off the print margins (sort of like elongating the field so that all chars are visisble in print preview mode).


Thank you!

0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12342571
Why not just preformat an excel spreadsheet template and then output the data to the template? That way you can ensure that your s/s is formatted exactly the way you want it.
0
 

Author Comment

by:LuckyLucks
ID: 12351350
Hi again,
    I have tried a bit of stuff myself and it does format the outputted query in Excel. The only problem is that I cant access the formatted Excel file simultaneously while Access is open. Any clues to this. Following is my code with amendments.
Private Sub Command71_Click()

 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
 "qryMontlyNews", "C:\" & "Report" & [Num] & ".xls"

 FormatReport "C:\" & "Report" & [Num] & ".xls"
End Sub

Private Sub FormatReport(Name As String)
   
 Dim xl As New Excel.Application
 Dim wb As Excel.Workbook
 Dim ws As Excel.Worksheet

 'Show Excel.  This is optional.
 xl.Visible = False

 'Open the workbook.
 Set wb = xl.Workbooks.Open(Name)

 'Get a reference to the first worksheet.
 Set ws = wb.Worksheets(1)
 
 'Set the column width for the News column.
 ws.Columns("E:E").Select
    Selection.ColumnWidth = 30
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Selection.Columns.AutoFit

'Set the column width for the Issuer column.
ws.Columns("C:C").Select
    Selection.ColumnWidth = 20
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Selection.Columns.AutoFit

 'Save the workbook.
 wb.Save

 'Close the workbook.
 wb.Close

 'Quit Excel.
 xl.Quit

 'Destroy all of the objects.
 Set ws = Nothing
 Set wb = Nothing
 Set xl = Nothing
End Sub
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

910 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now