Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Autofitting contents of an exported query from Access to Excel

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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

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.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

971 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