views:

25

answers:

1

please tell me how to change this access code so that radio buttons are removed and on clicking the import button itself the all 4 excel sheets are imported ( in other words code of all 4 radio buttons is excecuted ). I'll be glad if you can help me out however negative comments are most welcome as well :-)

Option Compare Database
Option Explicit


Private Sub cmdImport_Click()
On Error Resume Next
If IsNull(Me.txtFileName) Or Len(Me.txtFileName & "") = 0 Then
    MsgBox "please select the excel file"
    Me.cmdSelect.SetFocus
    Exit Sub
End If
Select Case Me.FrameSheet
    Case 1
    CurrentDb.Execute "delete * from Sheet1"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Sheet1", Me.txtFileName, True, "incoming calls!"
    Case 2
    CurrentDb.Execute "delete * from Sheet2"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Sheet2", Me.txtFileName, True, "incoming sms!"
    Case 3
    CurrentDb.Execute "delete * from Sheet3"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Sheet3", Me.txtFileName, True, "outgoing calls!"
    Case 4
    CurrentDb.Execute "delete * from Sheet4"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Sheet4", Me.txtFileName, True, "outgoing sms!"

    Case Else
    MsgBox "Select which Sheet you want to import"
    Exit Sub


End Select

End Sub

Private Sub cmdQuit_Click()
'    DoCmd.Quit
DoCmd.Close acForm, Me.Name
End Sub

Private Sub cmdSelect_Click()

    Dim strStartDir As String

    Dim strFilter As String
    Dim lngFlags As Long

    ' Lets start the file browse from our current directory

    strStartDir = CurrentDb.Name
    strStartDir = Left(strStartDir, Len(strStartDir) - Len(Dir(strStartDir)))


    strFilter = ahtAddFilterItem(strFilter, _
                        "Excel Files (*.xls)", "*.xls")
    Me.txtFileName = ahtCommonFileOpenSave(InitialDir:=strStartDir, _
                     Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
                     DialogTitle:="Select File")


End Sub
A: 

Just remove the actual select statement

something like this

Private Sub cmdImport_Click()
    On Error Resume Next
    If IsNull(Me.txtFileName) Or Len(Me.txtFileName & "") = 0 Then
        MsgBox "please select the excel file"
        Me.cmdSelect.SetFocus
        Exit Sub
    End If

        CurrentDb.Execute "delete * from Sheet1"
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Sheet1", Me.txtFileName, True, "incoming calls!"

        CurrentDb.Execute "delete * from Sheet2"
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Sheet2", Me.txtFileName, True, "incoming sms!"

        CurrentDb.Execute "delete * from Sheet3"
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Sheet3", Me.txtFileName, True, "outgoing calls!"

        CurrentDb.Execute "delete * from Sheet4"
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Sheet4", Me.txtFileName, True, "outgoing sms!"

    End Sub
astander