Full Version: Excel Macro using Date Range
UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel
blufeet
Hi everyone-

I am the process of writing my first macro in Excel. What I need to do is export data in an access database to an excel spreadsheet using a date range. I am struggling to figure out how I can use the date range from the excel spreadsheet. Basically people will go into the spreadsheet, put in a start and end date and click a button to export the data they need.

I also need the code to validate that the dates that are being entered are valid dates. this is what I have come up with so far to get the dates but i can't get it to validate the dates.

CODE
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim DTM_StartDate As Date
    Dim DTM_EndDate As Date
    
    DTM_StartDate = wks_SvcExport.Range("IN_StartDate")
    DTM_EndDate = wks_SvcExport.Range("IN_EndDate")
    
    If Not IsDate(DTM_StartDate) Then
        MsgBox "Please enter a valid Start Date", vbOKOnly, "Service Adjustments"
        Exit Sub
    End If

    If Not IsDate(DTM_EndDate) Then
        MsgBox "Please enter a valid End Date", vbOKOnly, "Service Adjustments"
        Exit Sub
    End If

    On Error GoTo SelectionError            'Error if multiple cells are selected
    
    Select Case True
        Case Target = Range("cmd_ExportFile"):       RS_ServiceAdjustData.Create_SW_Information DTM_StartDate, DTM_EndDate
    End Select
    
    On Error GoTo 0
    
    If Target.Address = Range("cmd_ExportFile") Then
        MsgBox "x", vbOKOnly, "Service Adjustments"
    End If
  
SelectionError:
    Application.EnableEvents = True
End Sub


when i put in something other than a date in the date fields, it doesn't go to message boxes. any help would be great?
dflak
You don't need a macro to do this. Ms-Query can do it for you. That is, it will read in data and then select it based on either: prompted criteria, "fixed" criteria (including formulas) or crteria read from cells (you can use data validation to enforce a correct date in the latter).
blufeet
Great thanks for the help on it. I am still relatively new to this stuff. I am glad there is an easier way to do it.
dflak
For a step-by-step on MS-Query, look here .
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.