Export to Excel - Replace a Range

[Ross]

Hi,

I have a formated excel worksheet that looks up data from a lookup tab. I
want to export new information for the lookup table from Access. I would
like to replace the tab without ruining the lookup formulas in the formatted
worksheet.

This article suggest that I can replace by "name range" if everything
matches perfectly. I have been unable to make it work (though this would be
ideal).

http://accessblog.net/2006/07/export-to-excel-range.html

Suggestions?

Thank you

Ross

 

[Klatuu]

Post the code you are using so we can have a look.

 

[Ross]

Dave,

Here is the Code:

'*********************************
Public Sub Export_Current_Data()
'*********************************
'Enrollment
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryChannelDIS_ENR2008", strPathFile, True, "EnrollChannel08"
MsgBox "done"

End Sub


, "EnrollChannel08" <----------This is the named Ranges in the Excel
spreadsheet and its size (rows and columns) exactly matches the Output from
the query called qryChannelDIS_ENR2008

The above code is posting the query data to a new tab called EnrollChannel08
and establishing the name range on that tab (deleting or removing the
previously established (correct) range on an original tab).

Thanks for any suggestions

Ross