Dale Fye
asked on
Excel automation from Access: Err# 462 , The remote server machine does not exist
I'm exporting an Access query to Excel and attempting to format some of the rows and columns.
Sometimes, this code works, other times, I get the "The remote server machine does not exist or is unavailable" error message on the line indicated. When it doesn't work, the header row is already populated with the column names.
Code is basically:
Sometimes, this code works, other times, I get the "The remote server machine does not exist or is unavailable" error message on the line indicated. When it doesn't work, the header row is already populated with the column names.
Code is basically:
Dim rs as dao.Recordset
Dim xl as object
dim wbk as object
dim sht as object
dim rs as object
set rs = currentdb.querydefs("myQueryName").openrecordset
set xl = createobject("Excel.Application")
set wbk = xl.workbooks.Add
set sht = wbk.sheets(1)
'Write the column headers
For intLoop = 1 to rs.fields.count
sht.cells(1, intLoop).Value = rs.fields(intloop-1).Name
Next
'Highlight the column headers and copy the header column
sht.Range("B1").Select
sht.Range(Selection, Selection.End(xlToRight)).Select 'this is the line that raises the error
set rng = Selection 'this is used later in the code
with rng.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
.PatternTintAndShade = 0
End With
sht.Range("A2").CopyFromRecordset rs
if NOT (rs is nothing) then
rs.close
set rs = nothing
end if
'*******************
'whole bunch of additional formatting functions here
'*******************
if NOT (rng is Nothing) then set rng = nothing
if NOT (sht is nothing) then set sht = nothing
if NOT (wbk is nothing) then set wbk = nothing
if NOT (xl is nothing) then set xl = nothing
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER