Exporting to Excel

  • Thread starter Thread starter Paul Cavacas
  • Start date Start date
P

Paul Cavacas

I have a routine that takes a DataTable and exports it to
Excel. Works fine and dandy on my machine, which has the
Office 11 Beta on it. But it of course doesn't work on
anybody else's machine because the have Office 2000 on
there machine. How can I make this export function work
with Office 2000. I only have the version 11
Type Library on my machine. I looked for another
version, but could not find it.
 
I have attached some old MS Access code that I had that works to export to
Excell. Maybe you can compare it to your code and see the deltas.

HTH,
Brian



HideControls True
ResetAircraftSelectors

strFile = "Computations"
strConnect = DLookup("Location", "tblFileLocation", "Use = " & "'" & strFile
& "'")
strIDCompany = Forms.frmCompanySelection.IDCompany

Screen.MousePointer = 11

ExcelRunning = IsExcelRunning()
boolOpen = True
If ExcelRunning Then
Set xlApp = GetObject(, "Excel.Application")
Else
Set xlApp = CreateObject("Excel.Application")
End If

Set MySheet = xlApp.workbooks.Open(strConnect)
Set sheet1 = MySheet.sheets("BASE MODEL")
Set sheet8 = MySheet.sheets("EPS")

Set rsExcel = _
CurrentDb.OpenRecordset("tblExcelValues", dbOpenDynaset)


For i = 1 To 6
AircraftSelection i
strAircraftNumber = i
Set rstAircraftNumber = CurrentDb.OpenRecordset _
("SELECT AircraftNumber, IDCompany FROM
tblAircraftEntry " & _
"WHERE AircraftNumber = " & strAircraftNumber &
_
"AND IDCompany = " & strIDCompany)
If rstAircraftNumber.RecordCount = 0 Then
rstAircraftNumber.Close
Set rstAircraftNumber = Nothing
Else
rstAircraftNumber.Close
Set rstAircraftNumber = Nothing
For Each ctl1 In Forms!frmAircraftEntry.Controls
If (ctl1.ControlType = acTextBox) _
Or (ctl1.ControlType = acListBox) _
Or (ctl1.ControlType = acComboBox) _
Then

With rsExcel
.FindFirst "FieldName='" & ctl1.ControlSource & "'"
If .NoMatch Then
lngRow = 1500
lngCol = 1
Else
On Error Resume Next
lngRow = rsExcel!XLRow
If i = 1 Then
lngCol = rsExcel!XLCol1
ElseIf i = 2 Then
lngCol = rsExcel!XLCol2
ElseIf i = 3 Then
lngCol = rsExcel!XLCol3
ElseIf i = 4 Then
lngCol = rsExcel!XLCol4
ElseIf i = 5 Then
lngCol = rsExcel!XLCol5
ElseIf i = 6 Then
lngCol = rsExcel!XLCol6
End If
On Error GoTo ErrorHandler
End If
End With
If ctl1.Tag = "NoUpdate" Then
ElseIf ctl1.Tag = "EPS" Then
sheet8.cells(lngRow, lngCol).Value = ctl1.Value
Else
sheet1.cells(lngRow, lngCol).Value = ctl1.Value
End If
End If
Next ctl1
End If
Next i

If Save = True Then
MySheet.Application.activeworkbook.Save
MySheet.Application.activeworkbook.Close
Else
MySheet.Application.activeworkbook.saved = True
MySheet.Application.activeworkbook.Close
End If
boolOpen = False

If Not ExcelRunning Then xlApp.Quit
Set MySheet = Nothing
Set sheet1 = Nothing
Set sheet8 = Nothing

rsExcel.Close
Set rsExcel = Nothing
Screen.MousePointer = 0

DoCmd.OpenForm "frmReportSelector"
DoCmd.OpenReport "rptBlank", acViewPreview

Exit Function


ExitProcedure:
On Error GoTo 0
If boolOpen Then
If Not ExcelRunning Then xlApp.Quit
Set MySheet = Nothing
Set sheet1 = Nothing
Set sheet8 = Nothing
rsExcel.Close
Set rsExcel = Nothing
End If
Screen.MousePointer = 0
If lngNumber <> 0 Then
Err.Raise lngNumber, strSource, strHelpFile, strHelpContext
End If
Exit Function

ErrorHandler:

With Err
lngNumber = .Number
strSource = .Source
strDescription = .Description
strHelpFile = .HelpFile
strHelpContext = .HelpContext
.Clear
End With
Resume ExitProcedure

End Function
 
You could either:
1. Import the type library on a machine that has Excel 2000 (using
tlbimp.exe). You may need to have the full .NET SDK installed for this
to work (i.e., it may not work with just the runtime).

2. If you create an HTML file containing only a <table> and a few rows
and cells, and rename that HTML file to .xls, then recent versions of
Excel (2000 and later, for sure; 97 might too) will open it just fine,
and will treat it just like an Excel file. Additionally, if you modify
that file and re-save it, it stays as HTML; you can use this to see the
tags that Excel uses for various formatting options. But do your
experimenting using the oldest version of Excel that you intend to
support, so you know it will be compatible.
 
Yes, I would agree with you. And yet another way is to create a tab
delimited file. The only problem with this is you loose some of the Excel
features.

Brian
 
Back
Top