Re: Export to Excel: 2nd, 3rd, etc excel files not opening up

Tech-Archive recommends: Fix windows errors by optimizing your registry

Ken,

The way I get around this is to use early binding as I develop my code.
Gives me access to Intellisense, and since my knowledge of the Excel object
model is extremely limited, I need all the Intellisense I can get.

Then, just prior to deployment, I change the Dim statements to Objects and
like Dave put a remark indicating what that object will actually be, making
it easy for me to go back to early binding if I have to.

HTH
Dale

"Ken Nguyen" <KenNguyen@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0ED7E8E0-7EAD-49F2-A4E6-E5AD61F5EBB6@xxxxxxxxxxxxxxxx
Thank you Dave for your response. However, I would like to stick with the
early bindings to use the built-in functions of excel to format my excel
sheets. I wasn't able to find anything about formatting excel using late
bindings and learn that it would be too difficult to re-write and re-code.

Well based on your suggestions my understanding is that in my early
binding
declarations I am trying to create a new instance of Excel.exe each time
the
code is ran so I am assuming to fix this is to check to see if the
instance
is already there if so reuse it else create a new one?

I think i got my understanding down however I don't know if my code meets
my
understanding please advise while still retaining the early bindings.

Public Sub ModifyExportedExcelFileFormats(sFile As String)
On Error GoTo Err_ModifyExportedExcelFileFormats

'Declare early binding variables
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xl*** As Excel.Work***

'excel application stuff
On Error Resume Next 'defer error trapping.
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlApp = New Excel.Application
End If
Err.Clear ' Clear Err object in case error occurred.
'Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Open(sFile)

'Declare late binding variables
'Private xlApp As Object
'Private xlBook As Object
'Private xl*** As Object

'Set xlApp = CreateObject("Excel.Application")
'Set xlBook = x1App.Workbooks.Open(sFile)
For Each xlSheet In xlBook.Worksheets

Thanks in advance for your help.
--
Ken


"Klatuu" wrote:

You code is trying to create an instance of Excel.exe, but after the
first
occurance, it is already running. what you need to do is first try to
use
the existing instance. If it doesn't exist, then create an instance:

On Error Resume Next ' Defer error trapping.
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlApp = CreateObject("excel.application")
End If
Err.Clear ' Clear Err object in case error occurred.

Also, you are using Early Binding. That is, defining the object as an
Excel
Application in your Dim statment. Although this may give you a slight
performance edge opening the first instance, it has issues. For example,
if
you compile you app on one version, but the target user changes versions
of
Excel, your app will fail. Using late binding, it loads whatever version
the
user has. Here are my Dims:

Private xlApp As Object 'Application Object
Private xlBook As Object 'Workbook Object
Private xl*** As Object 'Work*** Object

--
Dave Hargis, Microsoft Access MVP


"Ken Nguyen" wrote:

Ok, after a few weeks of trying to figure this out i've finally decided
to
ask the pros.

I created a tracking database that allowed for exporting to excel (this
is
done and works fine). I also was able to format that same exported
excel
*** that was created and it opens up after it has been formatted
(this
works perfectly). The problem I am having is after I export the first
excel
file any other exports after doesn't open up. It gets created and semi
formatted only. The first export is formatted in it's entirety to my
specifications, but all others get formatted (not in it's entirety or
opens
up). What could be the problem?

Here are parts of my codes (You may use this code for your application
as
long as you credit me):

Function: command button to export

'determines the path of where the database is
length = Len(CurrentProject.path)
For X = 1 To length
'finds \ and goes up one level
If Mid(CurrentProject.path, X, 1) = "\" Then
myValue = X
End If
Next X
'stores path to mypath
myPath = Left(CurrentProject.path, myValue)

'request user for a filename
myValue = InputBox("Please give your spread*** a filename (Make it
detailed): " & Chr(13) & "For example: 8-12-2007 Report of AB602",
"Saving
Spread*** File")

'exports to excel using transferspread***
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"Qry_SpreadsheetONEc", myValue, True
'title of spread*** for header
Forms!frmMenu!txtXLStitle = "Sort by Analyst for " & cmbOptions

'Calls function to format the excel workbook
Call ModifyExportedExcelFileFormats(myValue)

Public Sub ModifyExportedExcelFileFormats(sFile As String)
On Error GoTo Err_ModifyExportedExcelFileFormats

'Declare variables
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xl*** As Excel.Work***
'excel application stuff
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Open(sFile)
For Each xlSheet In xlBook.Worksheets

'Sets the header
rStart = "A1"
rEnd = "Y1"
'Sets the data
cStart = "A2"
cEnd = "Y" & txtNumRecords + 1 'added the plus 1 to include the
header
'Sets for repeat columns
rcStart = rStart
rcEnd = "B" & txtNumRecords + 1 'added the plus 1 to include
the
header

'freeze panes
xl***.Activate
xl***.Range("C2", "C2").Select
xlApp.ActiveWindow.FreezePanes = True

'set column widths
xl***.Range("A1").ColumnWidth = 9.57
xl***.Range("B1").ColumnWidth = 5.5
xl***.Range("C1").ColumnWidth = 12.43
xl***.Range("D1").ColumnWidth = 9.71
xl***.Range("E1").ColumnWidth = 14.57
xl***.Range("F1").ColumnWidth = 20
xl***.Range("G1").ColumnWidth = 13.5
xl***.Range("H1").ColumnWidth = 12.57
xl***.Range("I1").ColumnWidth = 16
xl***.Range("J1").ColumnWidth = 60
xl***.Range("K1").ColumnWidth = 60
xl***.Range("L1").ColumnWidth = 10
xl***.Range("M1").ColumnWidth = 13
xl***.Range("N1").ColumnWidth = 10
xl***.Range("O1").ColumnWidth = 10
xl***.Range("P1").ColumnWidth = 10
xl***.Range("Q1").ColumnWidth = 10
xl***.Range("R1").ColumnWidth = 10
xl***.Range("S1").ColumnWidth = 10
xl***.Range("T1").ColumnWidth = 15
xl***.Range("U1").ColumnWidth = 12
xl***.Range("V1").ColumnWidth = 15
xl***.Range("W1").ColumnWidth = 12
xl***.Range("X1").ColumnWidth = 6
xl***.Range("Y1").ColumnWidth = 60

'format header row
xl***.Range(rStart, rEnd).Font.Bold = True
xl***.Range(rStart, rEnd).Interior.ColorIndex = 15
xl***.Range(rStart, rEnd).WrapText = True
xl***.Range(rStart, rEnd).HorizontalAlignment = xlCenter

'format repeated columns
xl***.Range(rcStart, rcEnd).Font.Bold = True

'header row borders
xl***.Range(rStart, rEnd).Borders(xlLeft).Weight = xlThin
xl***.Range(rStart, rEnd).Borders(xlRight).Weight = xlThin
xl***.Range(rStart, rEnd).Borders(xlTop).Weight = xlThin
xl***.Range(rStart, rEnd).Borders(xlBottom).Weight = xlThin

'set header row height
xl***.Range(rStart).RowHeight = 45

'make columns wrap
xl***.Range(rStart, rEnd).WrapText = True
xl***.Range(cStart, cEnd).WrapText = True

'Autoformat the cells
xl***.Range(rStart, cEnd).Select
Selection.AutoFormat Format:=xlRangeAutoFormatList1,
Number:=False,
Font _
:=False, Alignment:=False, Border:=False, Pattern:=True,
Width:=False

'Creates borders around each cell
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

'Deselects all records and selects the first cell
xl***.Range(rStart).Select

'**********************************************
'** SETS UP THE PRINT SETUP
'**********************************************
'Sets page to landscape
xl***.PageSetup.Orientation = xlLandscape
'Repeat title rows
xl***.PageSetup.PrintTitleRows = "$1:$1"
'Repeat title columns
xl***.PageSetup.PrintTitleColumns = "$A:$B"
'Sets the Headers
xl***.PageSetup.LeftHeader = ""
xl***.PageSetup.CenterHeader = "IAS Tracking System - Redline
Tracking Log" & Chr(10) & "Fiscal Year " & cmbFiscalYear & Chr(10) &
Forms!frmMenu!txtXLStitle
xl***.PageSetup.RightHeader = ""
xl***.PageSetup.LeftFooter = "California Department of
Education"
xl***.PageSetup.CenterFooter = "Page &P of &N"
xl***.PageSetup.RightFooter = "&D - &T"
'Sets Margins
xl***.PageSetup.LeftMargin = 1
xl***.PageSetup.RightMargin = 1
xl***.PageSetup.TopMargin = 35
xl***.PageSetup.BottomMargin = 15
xl***.PageSetup.HeaderMargin = 10
xl***.PageSetup.FooterMargin = 5
'Sets Print Headings of the Columns i.e. A, B, C
xl***.PageSetup.PrintHeadings = False
'Sets Print Gridlines
xl***.PageSetup.PrintGridlines = True
xl***.PageSetup.PrintComments = xlPrintNoComments
'Printing of the records
xl***.PageSetup.CenterHorizontally = False
xl***.PageSetup.CenterVertically = False
'Sets page orientation
xl***.PageSetup.Orientation = xlLandscape
'Sets the paper size
xl***.PageSetup.PaperSize = xlPaperLetter
'Sets page number
xl***.PageSetup.FirstPageNumber = xlAutomatic
'Sets the order of the printing
xl***.PageSetup.Order = xlOverThenDown
'Sets to black and white printing
xl***.PageSetup.BlackAndWhite = False
xl***.PageSetup.Zoom = False
'Sets the page to print 1 page by x
xl***.PageSetup.FitToPagesWide = 2
xl***.PageSetup.FitToPagesTall = 9999
'Prints the errors or not
xl***.PageSetup.PrintErrors = xlPrintErrorsDisplayed
Next

'save file
xlBook.Save

'done
xlApp.Quit
Set xlApp = Nothing
Set xlBook = Nothing
Set xl*** = Nothing

Exit_ModifyExportedExcelFileFormats:
Call OpenExcelFile(sFile)

Exit Sub

Err_ModifyExportedExcelFileFormats:
vStatusBar = SysCmd(acSysCmdClearStatus)
'MsgBox Err.Number & " - " & Err.Description
Resume Exit_ModifyExportedExcelFileFormats
End Sub

Sub OpenExcelFile(strPathToFile As String)
Dim xlApp As Object

On Error Resume Next

Set xlApp = GetObject(, "Excel.Application")
Set xlApp = GetObject(strPathToFile)

xlApp.Application.Visible = True
xlApp.Parent.Windows(1).Visible = True

End Sub
--
a


.

Quantcast