Improve company productivity with a Business Account.Sign Up

x
?
Solved

Autofitting contents of an exported query from Access to Excel

Posted on 2004-10-18
4
Medium Priority
?
1,250 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 1000 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

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

606 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