Solved

command on form

Posted on 2002-05-09
3
197 Views
Last Modified: 2007-12-19
Hi there,

I have a command button that imports data from an excel
file.

"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?

Dentolas
0
Comment
Question by:dentolas
3 Comments
 
LVL 1

Expert Comment

by:dougp23
ID: 6999325
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:

Worksheets("Sheet1").Range("A1:G37").Clear


(My Excel VBA skills are a little shaky).
0
 
LVL 1

Author Comment

by:dentolas
ID: 7000783
Hi dougp23

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

Any ideas.
0
 
LVL 13

Accepted Solution

by:
John Mc Hale earned 100 total points
ID: 7012806
Try...

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
    wksExcel.Range(xlCellRange).Clear
    ' 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
    Else
        ' Save changes
        wkbExcel.Save
    End If
   
Exit_ClearExcelWorksheet:
    '
    ' 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
        objExcel.Quit
        ' Relase ref to Excel application object
        Set objExcel = Nothing
    End If
   
    ' Egg-timer Off
    DoCmd.Hourglass False
       
    Exit Sub
Err_ClearExcelWorksheet:
    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...

ClearExcel_Click()

...

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.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now