RPC Server Unavailable

[Ratman11]

I have some code that is exporting a query to excel & then formatting it. I am having a few issues here...if i try to reference the highlighted line to 'objex' i get the above error and it won't run, but if i don't reference it then excel remains running in the background and the workbook cannot be opened without manually stopping the .exe file. What am i doing wrong?

Public Function fcnxportsfcdeliverydates2()
Dim objex As Excel.Application
Set objex = New Excel.Application

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qrySFC_DeliveryDates", "H:\Service Centre\DataTeam\AccessOutputFiles\Reports\SFCDelivery\SFCdelivery2.xls", True

MsgBox "Export Completed. Click ok to continue."

objex.Workbooks.Open "H:\Service Centre\DataTeam\AccessOutputFiles\Reports\SFCDelivery\SFCdelivery2.xls"

objex.Worksheets("qrySFC_DeliveryDates").Cells.Select
objex.Selection.ColumnWidth = 14.43
objex.Worksheets("qrySFC_DeliveryDates").Cells.EntireRow.AutoFit
objex.Worksheets("qrySFC_DeliveryDates").Cells.EntireColumn.AutoFit
objex.Range("A2").Select
objex.ActiveWindow.FreezePanes = True

[highlight] Selection.Sort Key1:=Range("F2"), Order1:=xlAscending, Key2:=Range("H2") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False _
, Orientation:=xlTopToBottom [/highlight]
With objex.ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
objex.ActiveSheet.PageSetup.PrintArea = ""
With objex.ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&A"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page &P"
.RightFooter = ""
.LeftMargin = objex.InchesToPoints(0.75)
.RightMargin = objex.InchesToPoints(0.75)
.TopMargin = objex.InchesToPoints(1)
.BottomMargin = objex.InchesToPoints(1)
.HeaderMargin = objex.InchesToPoints(0.5)
.FooterMargin = objex.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
objex.ScreenUpdating = True
objex.Workbooks("SFCdelivery2.xls").Save
objex.Workbooks("SFCdelivery2.xls").Close
objex.Quit
Set objex = Nothing

 

[CautionMP]

This is a stab in the dark. I think there is something wrong with your range i.e. it is not a sortable range

objex.Range("A2").Select

Try changing the scope of your selection to entire columns

objex.Range("A:H").Select

You could also capture the number of records in your query and then hard code the number of rows

objex.Range("A2:H" & RecordCount).Select

Of course you will need to change the references to match the actual data you have.

Hope this helps,
CMP

 

[PHV]

objex.Selection.SortKey1...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ratman11]

found an even easier method..sorted the data in the access query first & then exported it!