Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Worksheets Prior To Access DoCmd.TransferSpreadsheet
Hi All, I know this is an Excel group, but it's an Excel related problem. I a using the DoCmd.TransferSpreadsheet command in Access (2000) t transfer query results to an Excel workbook. It transfers fine, but th problem I need to address is that I the transfer creates copies of th worksheet if one already exists. (e.g., transfer "qxlsTest" creates worksheet named "qxlsTest" if one does not exist, but create "qxlsTest1" if one already does.) I need to delete or overwrite th existing "qxlsTest" worksheet. I can access the Excel workbook fine from Access. I can manipulat worksheets (hide or change Cell values) fine, but I am not able t delete worksheets. It seems to process the code, but the delete par doesn't actually happen. Here's (part of) the code I'm using: (strFilename is the filename passed to this function) Dim xlApp As Object Dim wkb As Workbook Dim wks As Worksheet Set xlApp = CreateObject("Excel.Application") Set wkb = xlApp.Workbooks.Add(strFilename) xlApp.DisplayAlerts = False ' Iterate through all the worksheets in the workbook and delet existing ' worksheets as needed. For Each wks In wkb.Worksheets ' Check if the worksheet is an export from Access (prefix o "qxls"). If Left(wks.Name, 4) = "qxls" Then ' Delete the current worksheet. wks.Delete End If Next wks After the code above, the DoCmd.TransferSpreadsheet occurs. Again, tha part works alright. I'm just trying to delete or overwrite the existin "qxls" worksheets. Any help is greatly appreciated. I'm going nuts. Thanks -- wheatcracke ----------------------------------------------------------------------- wheatcracker's Profile: http://www.excelforum.com/member.php...fo&userid=3205 View this thread: http://www.excelforum.com/showthread.php?threadid=51806 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Access 2003: transferspreadsheet writes over existing spreadsheet | Excel Discussion (Misc queries) | |||
how to format a Docmd to pull in an Access file in Text format | Excel Programming | |||
how to format a Docmd to pull in an Access file in Text format | Excel Programming | |||
Export to Range from Access with TransferSpreadsheet | Excel Programming | |||
DoCmd.OpenReport problem | Excel Programming |