I'm using VBA to export an Access Query to an Excel Spreadsheet
But before exporting, I need to clear the contents of the spreadsheet as it may contain data that is outside the named range. If I don't delete all the rows, then I sometimes get an error 3434 - "Cannot Expand Named Range"
This is working fine, except that it also gets rid of all the formatting such as column width, shading, borders, etc...
How can I transfer the data to Excel without disturbing the spreedsheet formats.
Code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, WbSheet, CurrentProject.Path & "\" & WbName, True
But before exporting, I need to clear the contents of the spreadsheet as it may contain data that is outside the named range. If I don't delete all the rows, then I sometimes get an error 3434 - "Cannot Expand Named Range"
Code:
strFile = CurrentProject.Path & "\NameOfSpreesheet.xls"
If Len(Dir(strFile)) > 0 Then
Kill strFile
End If
This is working fine, except that it also gets rid of all the formatting such as column width, shading, borders, etc...
How can I transfer the data to Excel without disturbing the spreedsheet formats.