ADO and filter

M

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
'***********************************************************
 
R

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 said:
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
'***********************************************************
Click to expand...
 
K

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 said:
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
'***********************************************************
Click to expand...
 
M

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 said:
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
'***********************************************************
Click to expand...
Click to expand...
 
R

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... :(?
Click to expand...

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 said:
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 said:
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
'***********************************************************
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL not importing records from ACCDB 2
ADO access to excel workbook on website 1
SQL String 1
Problem when copying a range from closed workbooks (ADO) 4
Follow up question. Get value of closed file. 5
Compile Error (ADO) Ron De Bruin 2
Error With ADO (Starting to pull my hair out) 2
Copy Data from Excel 2007 XLSM workbook to Excel 2003 XLS workbook using ADO 2

Top