TransferSpreadsheet to specific sheet problem

[itchyII]

Hi Everyone,
OK, this is driving me crazy. I am trying to export a query to an existing Excel spreadsheet on a specific worksheet. I first clear the data on the worksheet (this works fine), then I transfer the spreadsheet. I am using the range parameter of the transfer spreadsheet action to specify the worksheet that I want to export to. Here's the wierd thing, I have the actual sheet "datas" where I want the data to go and I have created a dummy sheet I called "test". If I specify "test" in the transfer spreadsheet command, the data gets exported into the existing "test" sheet withour problems. If I specify "datas" in the the transfer spreadsheet command, the data gets exported in a new worksheet called "datas1". It will not go into the existing "datas" worksheet! I am assuming it might have something to do with the fact that I am manipulating the sheet prior to the transfer, but I complete all those actions and close and save the excel file prior to the transfer. I've been fudging with this since yesterday! I've tried all sorts of stuff! Does anyone have any ideas?

Access & Excel 2003

Private Sub Export_data_Click()
Dim strFile As String
Dim exc As New Excel.Application
Dim book As Excel.Workbook

With Me.cdlgOpen
    .ShowOpen
    strFile = .filename
End With

'open spreadsheet
Set exc = CreateObject("Excel.Application")
Set book = exc.Workbooks.Open(strFile)

'delete previous data
With book

    .Sheets("datas").Select
    .Sheets("datas").Range("A:AG").Select
    exc.Selection.Delete
    .Sheets("datas").Range("A1").Select

End With

'close and save, release objects
book.Close True
exc.Application.Quit
Set book = Nothing
Set exc = Nothing

'transfer data
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryShortage", strFile, True, "datas"

End Sub

Thanks!

ItchyII

 

[SkipVought]

Hi,

It would be real easy to configure your export, IN EXCEL as a query, that refreshes each time the workbook opens, using Data/Get External Data.

Skip,

_[red][/red]

 

[itchyII]

Thanks Skip, but the export of this data has to be manually triggered. The worksheet is tied to a pivot table which is tied to some charts, and so on. The table and associated charts should only be updated when a number of events have occured, which, do not always occur at a predictable time.

ItchyII