"The Remote Server Machine does not exist or is unavailable" ERROR 2

[tbg130]

Hi All,

I have some simple code for formatting a few ranges in Excel (data that was exported from Access). I have just started on this project and am learning lots... I realize that I should use a WITH ... END WITH statement for the following code, but for now, the important problem is that I get the above mentionned error when the code gets to the Third line (ActiveCell.EntireRow.Insert)...

Strange thing is that it does not occur every time; it also leaves on instance of Excel open at the end of the process (loops through a number of Workbooks, creates 9 worksheets in each, saves data, and closes all files).

Any other suggestions on ways to simplify this, improve it, or make it more robust is also appreciated...

Thanks,

Tyler


objSheet.Columns.AutoFit

objSheet.Cells(1, 1).Select
ActiveCell.EntireRow.Insert
objSheet.range(objSheet.Cells(1, 2), objSheet.Cells(1, 11)).Name = "quarterHeader"
objSheet.range("quarterHeader").Value = Array("1 Quarter", "2 Quarter", "3 Quarter", "4 Quarter", "2003", "1 Quarter", "2 Quarter", "3 Quarter", "4 Quarter", "2003")
objSheet.range("quarterHeader").Cells.BorderAround Weight:=xlMedium
objSheet.range("quarterHeader").BorderAround Weight:=xlMedium
objSheet.range("quarterHeader").HorizontalAlignment = xlCenter

objSheet.Cells(1, 1).Select
ActiveCell.EntireRow.Insert
objSheet.range(objSheet.Cells(1, 2), objSheet.Cells(1, 6)).Name = "Import"
objSheet.range("Import").Value = "Import"
range("Import", Cells(1, 6)).Merge
objSheet.range("Import").BorderAround Weight:=xlMedium
objSheet.range("Import").HorizontalAlignment = xlCenter

objSheet.range(objSheet.Cells(1, 7), objSheet.Cells(1, 11)).Name = "Export"
objSheet.range("Export").Value = "Export"
range("Export", Cells(1, 11)).Merge
objSheet.range("Export").BorderAround Weight:=xlMedium
objSheet.range("Export").HorizontalAlignment = xlCenter

objSheet.Cells(1, 1).Select
objSheet.range(objSheet.Cells(1, 1), objSheet.Cells(2, 1)).Name = "Participant"
range("Participant", Cells(2, 1)).Merge
objSheet.range("Participant").Value = "Participant"
objSheet.range("Participant").BorderAround Weight:=xlMedium

objSheet.Cells(1, 1).Select
ActiveCell.EntireRow.Insert
objSheet.range(objSheet.Cells(1, 1), objSheet.Cells(1, 11)).Name = "Subtitle"
range("Subtitle", Cells(1, 11)).Merge
objSheet.range("Subtitle").Value = "By Quarters & Annual"
objSheet.range("Subtitle").BorderAround Weight:=xlMedium
objSheet.range("Subtitle").HorizontalAlignment = xlCenter

objSheet.Cells(1, 1).Select
ActiveCell.EntireRow.Insert
objSheet.range(objSheet.Cells(1, 1), objSheet.Cells(1, 11)).Name = "Title"
range("Title", Cells(1, 11)).Merge
objSheet.range("Title").Value = "Table 4: Statistics by KPC (" & strCountryID & ") in " & strYear & ""
objSheet.range("Title").BorderAround Weight:=xlMedium
objSheet.range("Title").HorizontalAlignment = xlCenter
objSheet.range("Title").Font.Name = "Arial"
objSheet.range("Title").Font.Size = 10
objSheet.range("Title").Font.Bold = True

 

[PHV]

You have several implicit instantiations of the Excel.Application object in your code, like here:
ActiveCell.EntireRow.Insert
and here
range("Export", Cells(1, 11)).Merge
Don't forget to close and release your objects when the work is done.

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

 

[RoyVidar]

No - don't use With Blocks when automating Excel if there's any chance you're going to use late binding/createobject, Automation Doesn't Release Excel Object from Memory, the other reason for your "anomalities", are probably Excel automation fails second time code runs - i e you're using implicit referencing in stead of fully qualifying the reference.

For instance the ActiveCell reference, for the two samples here, I'm not entirely sure which object to reference thru, but try referencing starting with the Excel object variable, then the workbook variable, then the sheet... if not, fully qualified referencing thru some object variable, it leaves an open instance of Excel, fails on the second run...

[tt]objXL.ActiveCell.EntireRow.Insert[/tt]

and the Range and cells

[tt]objWrk.range("Import", objWrk.Cells(1, 6)).Merge[/tt]

- should be something to work on...

Roy-Vidar