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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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 xlSheet As Excel.Worksheet

'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 xlSheet 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 xlSheet As Object 'Worksheet 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
sheet 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 spreadsheet a filename (Make it
detailed): " & Chr(13) & "For example: 8-12-2007 Report of AB602", "Saving
Spreadsheet File")

'exports to excel using transferspreadsheet
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"Qry_SpreadsheetONEc", myValue, True
'title of spreadsheet 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 xlSheet As Excel.Worksheet
'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
xlSheet.Activate
xlSheet.Range("C2", "C2").Select
xlApp.ActiveWindow.FreezePanes = True

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

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

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

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

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

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

'Autoformat the cells
xlSheet.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
xlSheet.Range(rStart).Select

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

'save file
xlBook.Save

'done
xlApp.Quit
Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = 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
.

Relevant Pages

  • Re: Export to Excel: 2nd, 3rd, etc excel files not opening up
    ... my excel formatting code. ... Set xlApp = CreateObject ... 'format repeated columns ... xlSheet.Range.Borders.Weight = xlThin ...
    (microsoft.public.access.formscoding)
  • Re: Export to Excel: 2nd, 3rd, etc excel files not opening up
    ... The way I get around this is to use early binding as I develop my code. ... and since my knowledge of the Excel object ... Set xlApp = New Excel.Application ... xlSheet.Range.Borders.Weight = xlThin ...
    (microsoft.public.access.formscoding)
  • RE: Export to Excel: 2nd, 3rd, etc excel files not opening up
    ... There is no difference in the coding for early or late binding. ... different ways of establishing an excel object and manipulating it. ... Set xlApp = New Excel.Application ... xlSheet.Range.Borders.Weight = xlThin ...
    (microsoft.public.access.formscoding)
  • RE: Export to Excel: 2nd, 3rd, etc excel files not opening up
    ... Set xlApp = CreateObject ... defining the object as an Excel ... I also was able to format that same exported excel ... xlSheet.Range.Borders.Weight = xlThin ...
    (microsoft.public.access.formscoding)
  • Re: Excel Automation - Export TXT
    ... None of this worked to fix the problem, however, I looked up the constants for xlText and found that if I entered that, it worked. ... I even changed over to Early Binding and it would only accept the constant value. ... I am just new with Excel Automation ... Set xlApp = GetObject ...
    (microsoft.public.access.modulesdaovba)