The remote server machine does not exist or is navailable

[aldi07]

Hi,
I read many posts on the internet concerning the VBA error mentionned in the subject line, but none gave me a solution.
I am producing a series of graphs in excel, from access (office 2007). The first time, it runs perfectly well. When I run it a second time, I get:
"The remote server machine does not exist or is navailable" on a specific statement. If I close Access and restart the program, it runs well the first time, but always give me the error the second time.
Here are the relevant statements:

........
TWait = Time
TWait = DateAdd("s", 2, TWait)
Do Until TNow >= TWait
TNow = Time
Loop

Set xlObj = CreateObject("excel.application")
xlObj.Workbooks.Open MyDashboard
With xlObj
.Sheets("Dashboard 1 of 4").Select
.Range("A1").Select
.ActiveCell.FormulaR1C1 = Me.Category & " " & Me.Base
.Sheets("Dashboard 2 of 4").Select
.Range("A1").Select
.ActiveCell.FormulaR1C1 = Me.Category & " " & Me.Base
.Sheets("Dashboard 3 of 4").Select
.Range("A1").Select
.ActiveCell.FormulaR1C1 = Me.Category & " " & Me.Base
.Sheets("Dashboard 4 of 4").Select
.Range("A1").Select
.ActiveCell.FormulaR1C1 = Me.Category & " " & Me.Base
End With
'************************************************************************************************************
'Dashboard Creation
'************************************************************************************************************
MyTable1.Index = "PrimaryKey"
MyTable1.Seek "=", MyParamKey
Do Until MyTable1.EOF
'************************************************************************************
'* Getting tblVBAParams Parameters
'************************************************************************************
ReportID = MyTable1![ReportID]
ReportID_Pivot = Trim(ReportID & "_Pivot")
MyReportNo = MyTable1![MyReportNo]
Select Case MyReportNo
Case "101A", "102A"
GoTo MyNEXT
End Select
Col1Orientation = MyTable1![Col1Orientation]
Col2Orientation = MyTable1![Col2Orientation]
MyPageField = MyTable1![MyPageField]
MySecondPageField = MyTable1![MySecondPageField]
MyThirdPageField = MyTable1![MyThirdPageField]
MyRowField = MyTable1![MyRowField]
RepChartTitle = MyTable1![RepChartTitle]
If IsNull(MyTable1![RepAxisTitle]) Then
Else
RepAxisTitle = MyTable1![RepAxisTitle]
RepChartAxisStyle = MyTable1![RepChartAxisStyle]
End If
If IsNull(MyTable1![RepAxisSecondaryTitle]) Then
Else
RepAxisSecondaryTitle = MyTable1![RepAxisSecondaryTitle]
RepAxisSecondarySeries = MyTable1![RepAxisSecondarySeries]
RepSecondaryColorIndex = MyTable1![RepSecondaryColorIndex]
RepChartSecondaryAxisValueStyle = MyTable1![RepChartSecondaryAxisValueStyle]
End If
RepAxisPrimSerThous = MyTable1![RepAxisPrimSerThous]
RepAxisSecSerThous = MyTable1![RepAxisSecSerThous]
RepColorIndex = MyTable1![RepColorIndex]
RepChartTitleSize = MyTable1![RepChartTitleSize]
ChartObjectNo = MyTable1![ChartObjectNo]
DashboardSheet = MyTable1![DashboardSheet]
LegendTop = MyTable1![LegendTop]
PivotDest = Trim(ReportID_Pivot & "!R3C1")
MyPivotSheetName = MyTable1![MyPivotSheetName]
PivotName = Trim(ReportID & "!" & ReportID_Pivot)
MyTargetChartPos = MyTable1![MyTargetChartPos]
MyScaleWidth = MyTable1![MyScaleWidth]
MyScaleHeight = MyTable1![MyScaleHeight]
NbOfCols = MyTable1![NbOfCols]
MyXaxis = MyTable1![MyXaxis]
WeekDateInTitle = MyTable1![WeekDateInTitle]
'************************************************************************************
'Columns Titles, Pivot Calculation (xlSum or xlMax), Columns Types (xlLine or xlBar)
'************************************************************************************
Col1 = MyTable1![Col1]
Col2 = MyTable1![Col2]
Col3 = MyTable1![Col3]
Col4 = MyTable1![Col4]
Col5 = MyTable1![Col5]
Col6 = MyTable1![Col6]
Col7 = MyTable1![Col7]
Col8 = MyTable1![Col8]
Col9 = MyTable1![Col9]
Col1SM = MyTable1![Col1SM]
Col2SM = MyTable1![Col2SM]
Col3SM = MyTable1![Col3SM]
Col4SM = MyTable1![Col4SM]
Col5SM = MyTable1![Col5SM]
Col6SM = MyTable1![Col6SM]
Col7SM = MyTable1![Col7SM]
Col8SM = MyTable1![Col8SM]
Col9SM = MyTable1![Col9SM]
ColType(1) = MyTable1![Col1Type]
ColType(2) = MyTable1![Col2Type]
ColType(3) = MyTable1![Col3Type]
ColType(4) = MyTable1![Col4Type]
ColType(5) = MyTable1![Col5Type]
ColType(6) = MyTable1![Col6Type]
ColType(7) = MyTable1![Col7Type]
ColType(8) = MyTable1![Col8Type]
ColType(9) = MyTable1![Col9Type]
With xlObj
'.Application.ScreenUpdating = False
'.Visible = True
.Sheets(ReportID).Select
.Range("A1", ActiveCell.SpecialCells(xlLastCell)).Select
.ActiveWorkbook.Names.Add Name:=ReportID_Pivot, RefersToR1C1:=Selection
.Sheets.Add.Name = ReportID_Pivot
.ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
PivotName, Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:=PivotDest, TableName:=MyPivotSheetName, DefaultVersion _
:=xlPivotTableVersion12

etc...................

The error happens on the following statement:
.Range("A1", ActiveCell.SpecialCells(xlLastCell)).Select

Any idea?

Thank you.

 

[vbajock]

xlLastCell has no intrinsic meaning within Access. Replace it with the number 11, the literal value of the constant.

 

[PHV]

Just a guess:
.Range("A1", [highlight].[/highlight]ActiveCell.SpecialCells(xlLastCell)).Select
.ActiveWorkbook.Names.Add Name:=ReportID_Pivot, RefersToR1C1:=[highlight].[/highlight]Selection

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[aldi07]

Hi vbajock,
Thank you for your answer. How do I use the literal value of the constant? Could you re-write that specific statement for me?
Thank you in advance.

 

[vbajock]

SpecialCells(11)

xlLastCell is a constant built into the excel VBA environment, it has no meaning in Access

Put an Option Explicit statement at the top of your code module and Access will error out on these when you compile it

 

[aldi07]

The Option Explicit statement is there. I always use it in all my codes. There is no error whatsoever when I compile.
As for the statement, I replaced:

.Range("A1", ActiveCell.SpecialCells(xlLastCell)).Select

by:

.Range("A1", ActiveCell.SpecialCells(11)).Select

Unfortunately, as always, the first time, the code runs perfectly well, and produces the charts. The second time, I still get exactely the same error: The remote server machine does not exist or is unavailable...:-(

 

[aldi07]

I even replaced the whole statement by:
.ActiveCell.CurrentRegion.Select

Same problem: works the first time, get the same error message the second time...

 

[PHV]

Did you try my suggestion timestamped 19 Jun 12 17:59 ?

 

[vbajock]

Ok, I found this support KB:

http://support.microsoft.com/kb/319832

I think your application might he experiencing the same kind of issues discussed in the KB.

In a nut shell, the article says that object binding bugs in some MS internal function calls can bug out your code when you are looping thru objects, so all objects should be created using the create object method, which looks to me like how you are doing it, but it goes on to state to make sure you are not also declaring a reference to the object in the Tools/References pull down. Check it and remove any references to Excel if they are there, also, it suggests removing the Office Type Library as well, that might be all that there is to it. If not, it suggests going thru your code and re-engineering the binding and object declarations, like to replace anywhere you are reloading any object with the New method with a CreateObject call instead. If that doesn't work, there is some handy little code on this page that allows you to switch between late and early binding, you might want to put that in and see if that gets the bug out:

http://www.granite.ab.ca/access/latebinding.htm

Myself, I am pretty much an early binder. The code is easier to write and it seems more stable and faster. The disadvantage is that the references have to be reset if the user is using an older version of Access or Excel, but that is not an issue for me in my environment.

 

[aldi07]

Hi PHV, and vbajock,
Yes PHV, I did try your suggestion timestamped 19 Jun 12 17:59...
It is very bizare: it worked twice, then got the error on the third attempt, on another statement. I kept adding "dots" where it seemed appropriate.
After extensive testing, it always error after a couple or three attemps...
Unfortunately, this is definitely not a stable solution.
I am going to look into vbajock's suggestion about early and late binding. Being unfamilliar with it, I'll have to learn what it means first, though! I'll let you know if I hit any satisfying solution.
Thank you both for your time and your help.