Finicky VBA error - Application Objects

[ClimoC]

Hey peoples.

All my googling hasn't helped figure this out:

I'm comparing 2 excel books, and placing the differences in a third.

Code starts off with:

Dim xlapp As Object, oldxp As WorkBook, newxp As WorkBook, resultswb As WorkBook, _
xlsheet1 As Worksheet, xlsheet2 As Worksheet, resultsws As Worksheet
Set xlapp = CreateObject("Excel.Application")
xlapp.Visible = False
xlapp.Workbooks.Open FileName:="\\My old server file.xls"
Set oldxp = xlapp.ActiveWorkbook
Set xlsheet1 = xlapp.ActiveWorkbook.ActiveSheet
xlapp.Workbooks.Open FileName:="\\My new server file.xls"
Set newxp = xlapp.ActiveWorkbook
Set xlsheet2 = xlapp.ActiveWorkbook.ActiveSheet
xlapp.Workbooks.Add
Set resultswb = xlapp.ActiveWorkbook
Set resultsws = resultswb.ActiveSheet

Then I add all the values in the appropriate fields we are analysing into 4 or 5 scripting dictionaries, then compare them, and write the results to the third book.

I know that this is working, because after doing this through all the rows:
NB: compare and compareagain are the names of the Dictionaries, g and h are variants.

'(this is inside of a For Each loop)
resultrow = resultsws.Range("A30000:B30000").End(xlUp).row + 1
If compare.Item(h) <> compareagain.Item(g) Then
                resultsws.Cells(resultrow, 1) = g
                resultsws.Cells(resultrow, 2) = "Shot"
                resultsws.Cells(resultrow, 3) = "         Difference"

'resultrow' has incremented to 140, so it's obviously working.

But THEN, when I get to the very END of the procedure (this is where my inexplicable problem is):

This works:

resultsws.Cells(1, 1).EntireRow.Insert
resultsws.Range("A1").value = "Log Entry - performed by user: (" & uname & ") on " & timestamp
resultsws.Range("A1").Font.Italic = True
resultsws.Range("A2:C2").Font.Bold = True

BUT THIS FAILS:

resultsws.Range("A1:C" & Range("A30000:C30000").End(xlUp).row).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

and I get "Run-Time error '462': The remote server machine does not exist or is unavailable"

If I skip this line, is goes to the next bit, using the save object, and doesn't fail.

Again, when it gets to here:

resultsws.Range("A1:C" & Range("A30000:B30000").End(xlUp).row).Select
ExportToTextFile FName:=myfile, Sep:=Chr(10), SelectionOnly:=True, AppendData:=True

it fails with the same run-time error.



PLEASE HELP
PLEASE HELP
PLEASE HELP
PLEASE HELP
PLEASE HELP

 

[RoryA]

In those lines you have qualified the first Range call with the relevant worksheet, but not the other calls. For example - this:

resultsws.Range("A1:C" & Range("A30000:C30000").End(xlUp).row).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

should be:

resultsws.Range("A1:C" & resultsws.Range("A30000:C30000").End(xlUp).row).Sort Key1:=resultsws.Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

or put them all in a With...End With statement to save typing.

 

[ClimoC]

Argh! As usual, RoryA saves the day (I like rhyming)

I did notice that earlier, problem is that of the two 'resultsws' in blue you added rory, I had only done the second one. When it didn't make a difference, I changed it back.

All smooth sailing now. Thanks!!!

C