Fixed length when exporting to Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

With the Transferspreadsheet command or Output to, the columns in the Excel
files created has always a fixed length.
Is it possible to create the Excel file where the columns have the same
length as the table in Access?

See here the code:
DoCmd.OutputTo acOutputTable, "T_YTI", acFormatXLS, "D:/output.xls
or
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "T_YTI",
"D:\Output.XLS", True

I use Access and Excel 2000 under Windows XP
 
There is an additional code before exporting to Excel:
DoCmd.RunSQL " SELECT q_Output2.* " & _
" INTO T_YTI " & _
" FROM q_Output2 " & _
" WHERE (((q_Output2.Ktr)=""" & kantoor & """));"

The table T_YTI has also a fixed length of columns. So I think the question
is how to create a new table with the column length of the related query.
 
Hello Peter,

want you want can't be done the transferspreadsheet action, you have to
instantiate an new excel document, export the data to excel and afterwards
format the excel columns.

Example:
Dim appExcel As Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim db As DAO.Database
Dim r As DAO.Recordset

Set appExcel = New Excel.Application
Set xlWorkbook = appExcel.Workbooks.Add
Set xlSheet = xlWorkbook.Worksheets(1)
appExcel.UserControl = False
appExcel.DisplayAlerts = False
' Export the data to current Excel sheet!
Set db = CurrentDb
Set r = db.OpenRecordset("Your Query!!!", dbOpenSnapshot)

xlSheet .Cells(6, 1).CopyFromRecordset r
appExcel.Columns("A:A").EntireColumn.AutoFit
appExcel.Columns("E:E").ColumnWidth = 14

r.close
db.close
sFilename = Left(sFilename, Len(sFilename) - 4) & ".xls"
appExcel.ActiveWorkbook.SaveAs FileName:=sFilename
appExcel.UserControl = True
appExcel.DisplayAlerts = True
appExcel.Quit
Set xlSheet = Nothing
Set xlWorkbook = Nothing
Set appExcel = Nothing

You have to look up the relevant Excel Programming Codes in the Excel VBA
help, but the above example should work and output your data to excel. A good
start for an overwiew is to record macros in excel while formatting manualy
cells and to look and these macros. You have to be aware of "zombie"-excel
processes when you not correct reference to the excel methods!
 
Thanks Oliver for your solution.
However I think this is a very complicated one.

I did the sending of e-mails first with this code:
DoCmd.SendObject acSendQuery, "Q_output", acFormatXLS, mail, mail2
The XLS-file is not saved anyware and only attached to the mail. At that
moment the lay-out is perfect.

But I had to use the commands, because I have a problem with the Sendobject
command and I want to add the default mail signature.

Dim objolk As Outlook.Application
Dim objMailItem As Outlook.MailItem
etc.
and attach the file to the mail.
 
Back
Top