Open excel workbook from Access, clear cell contents

[Lifeseeker]

Hi there,

I would like to open an Excel workbook from MS Access and clear cell contents, or just delete some records in a specific worksheet.

If you open the test workbook, cell contents in RAW need to be deleted by calling from Access.

I have produced some code but it's partially working.

Sub TestFileOpened()
 
    ' Test to see if the file is open.
    If IsFileOpen("test.xls") Then
        ' Display a message stating the file in use.
        MsgBox "File already in use!"
        '
        '
    Else
        ' Display a message stating the file is not in use.
        MsgBox "File not in use!"
 
        'Open the excel file
        Dim xlApp As Object
        Set xlApp = CreateObject("Excel.Application")
 
        xlApp.Visible = True
 
        xlApp.Workbooks.Open "test.xls", True, False
 
   
            'With ActiveSheet
            lastRow = activesheet.usedrange.rows.Count
           
        'delete existing records
         Dim c As Integer
         Dim lastRow As Long
         Dim i As Long
         Dim j As Long
   
        For i = 1 To 100
            For j = 1 To lastRow
           
            worksheets("RAW").cells(i, j).Value = ""
            Next j
            Next i
        End With
       
       
        
        'export from access
       
    
    
        'close the workbook
       
        
        
    End If
 
End Sub
 
' This function checks to see if a file is open or not. If the file is
' already open, it returns True. If the file is not open, it returns
' False. Otherwise, a run-time error occurs because there is
' some other problem accessing the file.
 
Function IsFileOpen(filename As String)
    Dim filenum As Integer, errnum As Integer
 
    On Error Resume Next   ' Turn error checking off.
    filenum = FreeFile()   ' Get a free file number.
    ' Attempt to open the file and lock it.
    Open filename For Input Lock Read As #filenum
    Close filenum          ' Close the file.
    errnum = Err           ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.
 
    ' Check to see which error occurred.
    Select Case errnum
 
        ' No error occurred.
        ' File is NOT already open by another user.
        Case 0
         IsFileOpen = False
 
        ' Error number for "Permission Denied."
        ' File is already opened by another user.
        Case 70
            IsFileOpen = True
 
        ' Another error occurred.
        Case Else
            Error errnum
    End Select
 
End Function

If you put this in a standard module in access, the function works, but the part that doesn't work is where it says "activesheet". It somehow tries to recognize it as a variable, but it's not going to be a variable.

Can anyone help?

Thanks

 

[Mihail]

Take a look here. Hope this will help you.

 

[JHB]

I think it must be something like, (remember to change "C:\Access programmer\Test2003.xls" to where you have the Excel file"):

Sub TestFileOpened()
    Dim lastRow As Long
 
    ' Test to see if the file is open.
    If IsFileOpen("C:\Access programmer\Test2003.xls") Then
        ' Display a message stating the file in use.
        MsgBox "File already in use!"
    Else
        ' Display a message stating the file is not in use.
        MsgBox "File not in use!"
 
        'Open the excel file
        Dim xlApp As Object
        
        Set xlApp = CreateObject("Excel.Application")
        
        xlApp.Visible = True
        xlApp.Workbooks.Open "C:\Access programmer\Test2003.xls", True, False
        lastRow = xlApp.activesheet.usedrange.rows.Count
           
        'delete existing records
         Dim c As Integer
         Dim i As Long
         Dim j As Long
   
        For i = 1 To 100
          For j = 1 To lastRow
            xlApp.worksheets("RAW").cells(i, j).Value = ""
          Next j
        Next i
    End If
End Sub

 

[Lifeseeker]

great, thanks! It worked