Autofitting contents of an exported query from Access to Excel

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

LuckyLucksAsked:
Who is Participating?
 
PoshDogCommented:
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
 
LuckyLucksAuthor Commented:
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
 
shanesuebsahakarnCommented:
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
 
LuckyLucksAuthor Commented:
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
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.

All Courses

From novice to tech pro — start learning today.