Help with VBA

[m4dd0g]

OK i'm new to this VBA stuff so what i want to do is I got my excel workbook with data which was imported from a database and i want to create a form with two textboxes and a button which will search my workbook between to dates with i specify in the textboxes and then display that data on the sheet.

 

[SkipVought]

Hi,

Assuming that your imported data from your database is in table format, you could use MS Query to query yor table using the two date values as parameters in your query. (Assuming that you have a driver for the database from which you imported the data, you could even query THAT dataqbase DIRECTLY)

On a new sheet...

Data/Get External Data/New Database Query/Excel Files -- YOUR WORKBOOK -- YOUR SHEET containing the source data.....

Edit the Query and add 2 criteria for the dates.

In the VALUES row of the 2 criteria, enter
[tt]
[What From Date?]
[What Thru Date?]
[/tt]
respectively.

When you run the query, it will ask you for the dates.

On the sheet containing the query, Data/Get External Data/Parameters -- select the option to get the data from the sheet. Have your form put the dates on the sheet.


Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[m4dd0g]

ok the database have 65699 lines so i don't want to import that every time that i need to run my query i was thinking of using vba with the data in the workbook to filter out the data between two dates which will be faster then the query statemant

 

[PHV]

65699 lines
So forget Excel.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

I was NOT suggesting that you return the ENTIRE TABLE!

I suggested that you QUERY the table with the NECESSARY CRITERIA of interest to narrow down the resultset.

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[m4dd0g]

OK and how do i do that because i can only insert one criteria column with my query wizard

 

[SkipVought]

How so?

You can have more than one!

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[m4dd0g]

ok got that query thing right but it nothing from my other excel sheet don't now why but its got the headers but no data.

 

[SkipVought]

Then your criteria is not correct.

Make sure that you're entering real dates in the cells in Excel.

Why do Dates and Times seem to be so much trouble? faq68-5827

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[m4dd0g]

OK i understand how it uses the dates but i want to design a macro that would ask you to enter two different dates and then display the data which is in the range of that two dates and i want to do it with a macro because a macro starts up every time you run that excel sheet because that sheet is linked to a database by using odbc and what that sheet does now is everytime I run it it checks the data and compare it to the data in the database. If it is possible can you suply me with an example of how to do it with a macro.

 

[SkipVought]

Almost ANYTHING that you can do on a sheet can be recorded and run in a macro.

I do this sort of thing as a matter of course.

I'll either use a button click event, a combobox cilck event or a worksheet change event to run a macro that querys a database with criteria based on the parameters entered on the sheet.

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[SkipVought]

Here's an example of a query that I run from a Combobox Click Event. This line of code, picks up a value on a sheet...

    sSQL = sSQL & "Where Cells='" & [SelectedCell] & "' "

Sub GetPartList()
    Dim sPath As String, sDB As String
    Dim sConn As String, sSQL As String
    
    sPath = "\\bhdfwfp426.bh.textron.com\M_Ctr$\#MCOE Lean Mfg Database"
    
    sDB = "DSC PQPR's"
    
    sConn = "ODBC;DSN=MS Access Database;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".mdb;"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
    
    sSQL = "SELECT DISTINCT `Part Number` "
    sSQL = sSQL & "FROM `" & sPath & "\" & sDB & "`.xTBLAllPartsAtCells  "
    sSQL = sSQL & "Where Cells='" & [SelectedCell] & "' "
    
    With wsPartList
        With .QueryTables(1)
            .Connection = sConn
            .CommandText = sSQL
            .Refresh BackgroundQuery:=False
        End With
        Application.DisplayAlerts = False
        .[A1].CurrentRegion.CreateNames _
            Top:=True, Left:=False, Bottom:=False, Right:=False
        Application.DisplayAlerts = True
    End With
End Sub

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[m4dd0g]

OK that looks confusing and can i use that as a macro

 

[SkipVought]

Is the Pope Catholic?

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]