ADO and filter

[Martin Los]

I want to summarize a 180 MB Excel workbook that has 8 seperate worksheets
for each product we sell in our company. The Excel workbook is fed through
queries from Microsoft Navision. Each sheet has (almost) the same format:
Colum A Date
Column B Brand Family
Column C Value
Column D etcetera
....
Column AL user defined formulas

Column A to AK - query data
Column AL to AZ - user defined formula

I want to create a new workbook with a resume. Something like
Brand Month Value
==== ==== ====
Brand1 March 1000
Brand2 March 1500
Brand3 March 1000
Brand4 March 800
Brand5 March 2000
Brand6 March 750
Brand7 March 500
Brand8 March 4500

I based the following code on original code from Ron De Bruin:

'***********************************************************
Option Explicit
Const gszOUTPUTPATH As String = "\\Nas1500\RU\martin.los\Mis documentos\Temp\"
Const gszDATAFILE As String = "C:\Documents and
Settings\usuario\Escritorio\ESTADISTICA - DATOS.xls"

Sub GetData_Example_EstadisticasDatos()

Dim strDate As String
Dim strOutputFileName
Dim vSheetNames As Variant
Dim i As Integer

strDate = Format(Now, "dd-mmm-yy h-mm-ss")
strOutputFileName = gszOUTPUTPATH & strDate & " Resumen ventas.xls"
vSheetNames = Array("BRAND1", "BRAND2", "BRAND3", "BRAND4", "BRAND5",
"BRAND6", "BRAND7", "BRAND8")

Application.DisplayAlerts = False
Workbooks.Add
For i = 1 To Sheets.Count - 1
Sheets(i).Select
ActiveWindow.SelectedSheets.Delete
Next
ActiveSheet.Name = "Ventas " & strDate
With ActiveSheet
.Range("A1").Value = "BRAND"
.Range("B1").Value = "Month"
.Range("C1").Value = "Document"
Range("A1:C1").Font.Bold = True
For i = LBound(vSheetNames) To UBound(vSheetNames)
.Range("A" & i + 2).Value = vSheetNames(i)
Next
End With
Application.DisplayAlerts = True
'This code should be changed so it does not copy all data but the
summary of it
'meaning sort of sum(value) if month = 'march'
For i = LBound(vSheetNames) To UBound(vSheetNames)
GetData gszDATAFILE, _
vSheetNames(i), _
"A1:AL65000", _
ActiveSheet.Range("e1"), True
End Sub

'http://www.rondebruin.nl/ado.htm
Public Sub GetData(SourceFile As Variant, SourceSheet As String, _
sourceRange As String, TargetRange As Range, HeaderRow As
Boolean)
Dim rsData As ADODB.Recordset
Dim szConnect As String
Dim szSQL As String
Dim lCount As Long

If Range(sourceRange).Rows.Count = 1 Then
' Create the connection string.
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"
Else
' Create the connection string.
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes"";"
End If

'===> HOW CAN I GET A CONDITION HERE?
szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & sourceRange$ & "] WHERE
'Month' = 'March';"

??????????

' Clean up our Recordset object.
rsData.Close
Set rsData = Nothing
Exit Sub

End Sub
'***********************************************************

[Ron de Bruin]

Hi Martin

Never have done this with Excel files, only Access.
I try it this evening if i have time

Why no import your workbook in Access and use code from this page to get your information
http://www.rondebruin.nl/accessexcel.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Martin Los" <[email protected]> wrote in message news:[email protected]...
>I want to summarize a 180 MB Excel workbook that has 8 seperate worksheets
> for each product we sell in our company. The Excel workbook is fed through
> queries from Microsoft Navision. Each sheet has (almost) the same format:
> Colum A Date
> Column B Brand Family
> Column C Value
> Column D etcetera
> ...
> Column AL user defined formulas
>
> Column A to AK - query data
> Column AL to AZ - user defined formula
>
> I want to create a new workbook with a resume. Something like
> Brand Month Value
> ==== ==== ====
> Brand1 March 1000
> Brand2 March 1500
> Brand3 March 1000
> Brand4 March 800
> Brand5 March 2000
> Brand6 March 750
> Brand7 March 500
> Brand8 March 4500
>
> I based the following code on original code from Ron De Bruin:
>
> '***********************************************************
> Option Explicit
> Const gszOUTPUTPATH As String = "\\Nas1500\RU\martin.los\Mis documentos\Temp\"
> Const gszDATAFILE As String = "C:\Documents and
> Settings\usuario\Escritorio\ESTADISTICA - DATOS.xls"
>
> Sub GetData_Example_EstadisticasDatos()
>
> Dim strDate As String
> Dim strOutputFileName
> Dim vSheetNames As Variant
> Dim i As Integer
>
> strDate = Format(Now, "dd-mmm-yy h-mm-ss")
> strOutputFileName = gszOUTPUTPATH & strDate & " Resumen ventas.xls"
> vSheetNames = Array("BRAND1", "BRAND2", "BRAND3", "BRAND4", "BRAND5",
> "BRAND6", "BRAND7", "BRAND8")
>
> Application.DisplayAlerts = False
> Workbooks.Add
> For i = 1 To Sheets.Count - 1
> Sheets(i).Select
> ActiveWindow.SelectedSheets.Delete
> Next
> ActiveSheet.Name = "Ventas " & strDate
> With ActiveSheet
> .Range("A1").Value = "BRAND"
> .Range("B1").Value = "Month"
> .Range("C1").Value = "Document"
> Range("A1:C1").Font.Bold = True
> For i = LBound(vSheetNames) To UBound(vSheetNames)
> .Range("A" & i + 2).Value = vSheetNames(i)
> Next
> End With
> Application.DisplayAlerts = True
> 'This code should be changed so it does not copy all data but the
> summary of it
> 'meaning sort of sum(value) if month = 'march'
> For i = LBound(vSheetNames) To UBound(vSheetNames)
> GetData gszDATAFILE, _
> vSheetNames(i), _
> "A1:AL65000", _
> ActiveSheet.Range("e1"), True
> End Sub
>
> 'http://www.rondebruin.nl/ado.htm
> Public Sub GetData(SourceFile As Variant, SourceSheet As String, _
> sourceRange As String, TargetRange As Range, HeaderRow As
> Boolean)
> Dim rsData As ADODB.Recordset
> Dim szConnect As String
> Dim szSQL As String
> Dim lCount As Long
>
> If Range(sourceRange).Rows.Count = 1 Then
> ' Create the connection string.
> szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & SourceFile & ";" & _
> "Extended Properties=""Excel 8.0;HDR=No"";"
> Else
> ' Create the connection string.
> szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & SourceFile & ";" & _
> "Extended Properties=""Excel 8.0;HDR=Yes"";"
> End If
>
> '===> HOW CAN I GET A CONDITION HERE?
> szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & sourceRange$ & "] WHERE
> 'Month' = 'March';"
>
> ??????????
>
> ' Clean up our Recordset object.
> rsData.Close
> Set rsData = Nothing
> Exit Sub
>
> End Sub
> '***********************************************************
>

[K Dales]

ADO uses SQL and all conditions go in the WHERE clause. You already have one
condition: WHERE 'Month' = 'March'; you can add other conditions with AND
and/or OR statements, such as
"... WHERE 'Month' = 'March' AND ('Brand' = 'Brand1' OR 'Brand' = 'Brand4');"
so by following the form of this example you should be able to select just
about any combination of data from your source range.
--
- K Dales


"Martin Los" wrote:

> I want to summarize a 180 MB Excel workbook that has 8 seperate worksheets
> for each product we sell in our company. The Excel workbook is fed through
> queries from Microsoft Navision. Each sheet has (almost) the same format:
> Colum A Date
> Column B Brand Family
> Column C Value
> Column D etcetera
> ...
> Column AL user defined formulas
>
> Column A to AK - query data
> Column AL to AZ - user defined formula
>
> I want to create a new workbook with a resume. Something like
> Brand Month Value
> ==== ==== ====
> Brand1 March 1000
> Brand2 March 1500
> Brand3 March 1000
> Brand4 March 800
> Brand5 March 2000
> Brand6 March 750
> Brand7 March 500
> Brand8 March 4500
>
> I based the following code on original code from Ron De Bruin:
>
> '***********************************************************
> Option Explicit
> Const gszOUTPUTPATH As String = "\\Nas1500\RU\martin.los\Mis documentos\Temp\"
> Const gszDATAFILE As String = "C:\Documents and
> Settings\usuario\Escritorio\ESTADISTICA - DATOS.xls"
>
> Sub GetData_Example_EstadisticasDatos()
>
> Dim strDate As String
> Dim strOutputFileName
> Dim vSheetNames As Variant
> Dim i As Integer
>
> strDate = Format(Now, "dd-mmm-yy h-mm-ss")
> strOutputFileName = gszOUTPUTPATH & strDate & " Resumen ventas.xls"
> vSheetNames = Array("BRAND1", "BRAND2", "BRAND3", "BRAND4", "BRAND5",
> "BRAND6", "BRAND7", "BRAND8")
>
> Application.DisplayAlerts = False
> Workbooks.Add
> For i = 1 To Sheets.Count - 1
> Sheets(i).Select
> ActiveWindow.SelectedSheets.Delete
> Next
> ActiveSheet.Name = "Ventas " & strDate
> With ActiveSheet
> .Range("A1").Value = "BRAND"
> .Range("B1").Value = "Month"
> .Range("C1").Value = "Document"
> Range("A1:C1").Font.Bold = True
> For i = LBound(vSheetNames) To UBound(vSheetNames)
> .Range("A" & i + 2).Value = vSheetNames(i)
> Next
> End With
> Application.DisplayAlerts = True
> 'This code should be changed so it does not copy all data but the
> summary of it
> 'meaning sort of sum(value) if month = 'march'
> For i = LBound(vSheetNames) To UBound(vSheetNames)
> GetData gszDATAFILE, _
> vSheetNames(i), _
> "A1:AL65000", _
> ActiveSheet.Range("e1"), True
> End Sub
>
> 'http://www.rondebruin.nl/ado.htm
> Public Sub GetData(SourceFile As Variant, SourceSheet As String, _
> sourceRange As String, TargetRange As Range, HeaderRow As
> Boolean)
> Dim rsData As ADODB.Recordset
> Dim szConnect As String
> Dim szSQL As String
> Dim lCount As Long
>
> If Range(sourceRange).Rows.Count = 1 Then
> ' Create the connection string.
> szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & SourceFile & ";" & _
> "Extended Properties=""Excel 8.0;HDR=No"";"
> Else
> ' Create the connection string.
> szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & SourceFile & ";" & _
> "Extended Properties=""Excel 8.0;HDR=Yes"";"
> End If
>
> '===> HOW CAN I GET A CONDITION HERE?
> szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & sourceRange$ & "] WHERE
> 'Month' = 'March';"
>
> ??????????
>
> ' Clean up our Recordset object.
> rsData.Close
> Set rsData = Nothing
> Exit Sub
>
> End Sub
> '***********************************************************
>

[Martin Los]

Ron and KDales:

Thanks for your answers.
Ron, I do not use Access because at my work we do not have the expertise (I
know Excel far better, although I know access could be used better for a lot
of database functions. Alas, that´s just the way it is .....)

K Dales, the problem is just that the condition WHERE 'Month' = 'March' does
not work. I get an error. So now my question is: with 'Month' being the title
of the column I want to use to filter the data, what can be wrong with the
formula?

I should add that we use SQL SERVER for our Navision database since about a
month. Maybe it would be better to attack the server than to query Navision,
paste the ruseult in excel and than attack excel as a database... ?

TIA

Martin
"K Dales" escribió:

> ADO uses SQL and all conditions go in the WHERE clause. You already have one
> condition: WHERE 'Month' = 'March'; you can add other conditions with AND
> and/or OR statements, such as
> "... WHERE 'Month' = 'March' AND ('Brand' = 'Brand1' OR 'Brand' = 'Brand4');"
> so by following the form of this example you should be able to select just
> about any combination of data from your source range.
> --
> - K Dales
>
>
> "Martin Los" wrote:
>
> > I want to summarize a 180 MB Excel workbook that has 8 seperate worksheets
> > for each product we sell in our company. The Excel workbook is fed through
> > queries from Microsoft Navision. Each sheet has (almost) the same format:
> > Colum A Date
> > Column B Brand Family
> > Column C Value
> > Column D etcetera
> > ...
> > Column AL user defined formulas
> >
> > Column A to AK - query data
> > Column AL to AZ - user defined formula
> >
> > I want to create a new workbook with a resume. Something like
> > Brand Month Value
> > ==== ==== ====
> > Brand1 March 1000
> > Brand2 March 1500
> > Brand3 March 1000
> > Brand4 March 800
> > Brand5 March 2000
> > Brand6 March 750
> > Brand7 March 500
> > Brand8 March 4500
> >
> > I based the following code on original code from Ron De Bruin:
> >
> > '***********************************************************
> > Option Explicit
> > Const gszOUTPUTPATH As String = "\\Nas1500\RU\martin.los\Mis documentos\Temp\"
> > Const gszDATAFILE As String = "C:\Documents and
> > Settings\usuario\Escritorio\ESTADISTICA - DATOS.xls"
> >
> > Sub GetData_Example_EstadisticasDatos()
> >
> > Dim strDate As String
> > Dim strOutputFileName
> > Dim vSheetNames As Variant
> > Dim i As Integer
> >
> > strDate = Format(Now, "dd-mmm-yy h-mm-ss")
> > strOutputFileName = gszOUTPUTPATH & strDate & " Resumen ventas.xls"
> > vSheetNames = Array("BRAND1", "BRAND2", "BRAND3", "BRAND4", "BRAND5",
> > "BRAND6", "BRAND7", "BRAND8")
> >
> > Application.DisplayAlerts = False
> > Workbooks.Add
> > For i = 1 To Sheets.Count - 1
> > Sheets(i).Select
> > ActiveWindow.SelectedSheets.Delete
> > Next
> > ActiveSheet.Name = "Ventas " & strDate
> > With ActiveSheet
> > .Range("A1").Value = "BRAND"
> > .Range("B1").Value = "Month"
> > .Range("C1").Value = "Document"
> > Range("A1:C1").Font.Bold = True
> > For i = LBound(vSheetNames) To UBound(vSheetNames)
> > .Range("A" & i + 2).Value = vSheetNames(i)
> > Next
> > End With
> > Application.DisplayAlerts = True
> > 'This code should be changed so it does not copy all data but the
> > summary of it
> > 'meaning sort of sum(value) if month = 'march'
> > For i = LBound(vSheetNames) To UBound(vSheetNames)
> > GetData gszDATAFILE, _
> > vSheetNames(i), _
> > "A1:AL65000", _
> > ActiveSheet.Range("e1"), True
> > End Sub
> >
> > 'http://www.rondebruin.nl/ado.htm
> > Public Sub GetData(SourceFile As Variant, SourceSheet As String, _
> > sourceRange As String, TargetRange As Range, HeaderRow As
> > Boolean)
> > Dim rsData As ADODB.Recordset
> > Dim szConnect As String
> > Dim szSQL As String
> > Dim lCount As Long
> >
> > If Range(sourceRange).Rows.Count = 1 Then
> > ' Create the connection string.
> > szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> > "Data Source=" & SourceFile & ";" & _
> > "Extended Properties=""Excel 8.0;HDR=No"";"
> > Else
> > ' Create the connection string.
> > szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> > "Data Source=" & SourceFile & ";" & _
> > "Extended Properties=""Excel 8.0;HDR=Yes"";"
> > End If
> >
> > '===> HOW CAN I GET A CONDITION HERE?
> > szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & sourceRange$ & "] WHERE
> > 'Month' = 'March';"
> >
> > ??????????
> >
> > ' Clean up our Recordset object.
> > rsData.Close
> > Set rsData = Nothing
> > Exit Sub
> >
> > End Sub
> > '***********************************************************
> >

[Ron de Bruin]

Hi Martin

> I should add that we use SQL SERVER for our Navision database since about a
> month. Maybe it would be better to attack the server than to query Navision,
> paste the ruseult in excel and than attack excel as a database... ?

I think that this is the best choice
But a excel workbook of 180mb is very big ?



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Martin Los" <[email protected]> wrote in message news:[email protected]...
> Ron and KDales:
>
> Thanks for your answers.
> Ron, I do not use Access because at my work we do not have the expertise (I
> know Excel far better, although I know access could be used better for a lot
> of database functions. Alas, that´s just the way it is .....)
>
> K Dales, the problem is just that the condition WHERE 'Month' = 'March' does
> not work. I get an error. So now my question is: with 'Month' being the title
> of the column I want to use to filter the data, what can be wrong with the
> formula?
>
> I should add that we use SQL SERVER for our Navision database since about a
> month. Maybe it would be better to attack the server than to query Navision,
> paste the ruseult in excel and than attack excel as a database... ?
>
> TIA
>
> Martin
> "K Dales" escribió:
>
>> ADO uses SQL and all conditions go in the WHERE clause. You already have one
>> condition: WHERE 'Month' = 'March'; you can add other conditions with AND
>> and/or OR statements, such as
>> "... WHERE 'Month' = 'March' AND ('Brand' = 'Brand1' OR 'Brand' = 'Brand4');"
>> so by following the form of this example you should be able to select just
>> about any combination of data from your source range.
>> --
>> - K Dales
>>
>>
>> "Martin Los" wrote:
>>
>> > I want to summarize a 180 MB Excel workbook that has 8 seperate worksheets
>> > for each product we sell in our company. The Excel workbook is fed through
>> > queries from Microsoft Navision. Each sheet has (almost) the same format:
>> > Colum A Date
>> > Column B Brand Family
>> > Column C Value
>> > Column D etcetera
>> > ...
>> > Column AL user defined formulas
>> >
>> > Column A to AK - query data
>> > Column AL to AZ - user defined formula
>> >
>> > I want to create a new workbook with a resume. Something like
>> > Brand Month Value
>> > ==== ==== ====
>> > Brand1 March 1000
>> > Brand2 March 1500
>> > Brand3 March 1000
>> > Brand4 March 800
>> > Brand5 March 2000
>> > Brand6 March 750
>> > Brand7 March 500
>> > Brand8 March 4500
>> >
>> > I based the following code on original code from Ron De Bruin:
>> >
>> > '***********************************************************
>> > Option Explicit
>> > Const gszOUTPUTPATH As String = "\\Nas1500\RU\martin.los\Mis documentos\Temp\"
>> > Const gszDATAFILE As String = "C:\Documents and
>> > Settings\usuario\Escritorio\ESTADISTICA - DATOS.xls"
>> >
>> > Sub GetData_Example_EstadisticasDatos()
>> >
>> > Dim strDate As String
>> > Dim strOutputFileName
>> > Dim vSheetNames As Variant
>> > Dim i As Integer
>> >
>> > strDate = Format(Now, "dd-mmm-yy h-mm-ss")
>> > strOutputFileName = gszOUTPUTPATH & strDate & " Resumen ventas.xls"
>> > vSheetNames = Array("BRAND1", "BRAND2", "BRAND3", "BRAND4", "BRAND5",
>> > "BRAND6", "BRAND7", "BRAND8")
>> >
>> > Application.DisplayAlerts = False
>> > Workbooks.Add
>> > For i = 1 To Sheets.Count - 1
>> > Sheets(i).Select
>> > ActiveWindow.SelectedSheets.Delete
>> > Next
>> > ActiveSheet.Name = "Ventas " & strDate
>> > With ActiveSheet
>> > .Range("A1").Value = "BRAND"
>> > .Range("B1").Value = "Month"
>> > .Range("C1").Value = "Document"
>> > Range("A1:C1").Font.Bold = True
>> > For i = LBound(vSheetNames) To UBound(vSheetNames)
>> > .Range("A" & i + 2).Value = vSheetNames(i)
>> > Next
>> > End With
>> > Application.DisplayAlerts = True
>> > 'This code should be changed so it does not copy all data but the
>> > summary of it
>> > 'meaning sort of sum(value) if month = 'march'
>> > For i = LBound(vSheetNames) To UBound(vSheetNames)
>> > GetData gszDATAFILE, _
>> > vSheetNames(i), _
>> > "A1:AL65000", _
>> > ActiveSheet.Range("e1"), True
>> > End Sub
>> >
>> > 'http://www.rondebruin.nl/ado.htm
>> > Public Sub GetData(SourceFile As Variant, SourceSheet As String, _
>> > sourceRange As String, TargetRange As Range, HeaderRow As
>> > Boolean)
>> > Dim rsData As ADODB.Recordset
>> > Dim szConnect As String
>> > Dim szSQL As String
>> > Dim lCount As Long
>> >
>> > If Range(sourceRange).Rows.Count = 1 Then
>> > ' Create the connection string.
>> > szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>> > "Data Source=" & SourceFile & ";" & _
>> > "Extended Properties=""Excel 8.0;HDR=No"";"
>> > Else
>> > ' Create the connection string.
>> > szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>> > "Data Source=" & SourceFile & ";" & _
>> > "Extended Properties=""Excel 8.0;HDR=Yes"";"
>> > End If
>> >
>> > '===> HOW CAN I GET A CONDITION HERE?
>> > szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & sourceRange$ & "] WHERE
>> > 'Month' = 'March';"
>> >
>> > ??????????
>> >
>> > ' Clean up our Recordset object.
>> > rsData.Close
>> > Set rsData = Nothing
>> > Exit Sub
>> >
>> > End Sub
>> > '***********************************************************
>> >