Solved

Autofitting contents of an exported query from Access to Excel

Posted on 2004-10-18
4
1,152 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

743 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

10 Experts available now in Live!

Get 1:1 Help Now