• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

command on form

Hi there,

I have a command button that imports data from an excel

"Private Sub Command1_Click()
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "AccPakImport", "C:\Marieta\MImport", False, "A1:O10000"
End Sub"

What I would like to do in Access is to empty the excel
file immediately after that the data is imported.
Is this possible and if so can you suggest the coding?

1 Solution
You could delete the spreadsheet. That's fairly easy with the KILL command.  If the spreadsheet gets created each time then it won't be a problem.  If it doesn't, then you would want to clean out the cells.

Not sure, but something like this:


(My Excel VBA skills are a little shaky).
dentolasAuthor Commented:
Hi dougp23

Your suggesttion is the code that you would use in Excel.
I would like to do it via Access.

Any ideas.
John Mc HaleForensic Computer Examiner, Analyst/Programmer & Database ArchitectCommented:

Add another button to your form to Clear the contents of the Excel Worksheet.

In the class module for the form, add the following subroutine...

Private Sub ClearExcelWorksheet(xlFilename As String, Optional xlWorksheetname As String = "Sheet1", Optional xlCellRange As String = "A1:IV65536")
On Error GoTo Err_ClearExcelWorksheet

    Const ProcName As String = "ClearExcelWorksheet"
    Dim objExcel As Excel.Application
    Dim wkbExcel As Excel.Workbook
    Dim wksExcel As Excel.Worksheet
    ' Egg-timer On
    DoCmd.Hourglass True
    ' Start Excel OLE Server
    Set objExcel = New Excel.Application
    ' Open workbook in Excel
    Set wkbExcel = objExcel.Workbooks().Open(xlFilename)
    ' Set worksheet object
    Set wksExcel = wkbExcel.Worksheets(xlWorksheetname)
    ' Clear named range
    ' Check if someone else already has workbook open for editing
    If wkbExcel.ReadOnly Then
        ' Show 'read-only' message
        MsgBox "The Excel workbook file '" & xlFilename & "' is already" & vbCrLf & _
               "open exclusively by another process and cannot be cleared at this time!", vbExclamation, ProcName
        ' Save changes
    End If
    ' Tidy up
    ' Release ref to worksheet object
    Set wksExcel = Nothing
    If Not wkbExcel Is Nothing Then
        ' Close workbook
        wkbExcel.Close False, xlFilename
        ' Release ref to workbook object
        Set wkbExcel = Nothing
    End If
    If Not objExcel Is Nothing Then
        ' Quit application
        ' Relase ref to Excel application object
        Set objExcel = Nothing
    End If
    ' Egg-timer Off
    DoCmd.Hourglass False
    Exit Sub
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Exit_ClearExcelWorksheet

End Sub

Now, clearing your Excel worksheet should be as easy as calling the subroutine from the Click event of your new button...



Call ClearExcelWorksheet("C:\Marieta\MImport.xls", "AccPakImport")

... I am assuming that your import spreadsheet is called "MImport.xls" and the worksheet containing the data is called "AccPakImport".

**** IMPORTANT ****

Include a reference to the Excel Object Library (I used 8.0 for this example) for this to work.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now