Data available for plotting vs already plotted data

[L Mehl]

Hello --

This charting application has the following features:

1 - user can "import" a time series (into a set of columns in a worksheet);
the app automatically adds a plot of the data to the chart

2 - user can delete from the chart a series which was plotted in 1; the
source data is not deleted from the worksheet which holds it

3 - user can decide later to add to the chart a series deleted in 2

Problem:
I want to present in a listbox the names/legends of the "imported but not
plotted" series so the user can select one for 3

If I could use SQL in this app, the source for the listbox would look
something like:

SELECT name_Imported FROM tblImported
WHERE name_Imported NOT IN (SELECT name_Plotted FROM tblPlotted)

Example:
Imported Plotted
------------ ----------
series1 series1
series2 series4
series3
series4
series5

The listpox would contain the values:
series2
series3
series5

Each "tbl..." would, I assume, be a range in one of the application's
worksheets, and filled by a process I don't know how to code.

Can anyone suggest how to populate such a listbox?

Thanks for any help.

Larry Mehl

 

[onedaywhen]

...

If I could use SQL in this app, the source for the listbox would look
something like:

SELECT name_Imported FROM tblImported
WHERE name_Imported NOT IN (SELECT name_Plotted FROM tblPlotted)

Example:
Imported Plotted
------------ ----------
series1 series1
series2 series4
series3
series4
series5

The listpox would contain the values:
series2
series3
series5

The more usual SQL construct would be to use an OUTER JOIN:

SELECT T2.name_Plotted
FROM [tblImported] T1
LEFT JOIN [tblPlotted] T2
ON T1.name_Imported = T2.name_Plotted
WHERE T2.name_Plotted IS NOT NULL

You can access the data in a workbook using ADO but due to the dreaded
memory leak bug, you can't query an open workbook. Happily, the
workaround is easy enough: save a copy of the relevant worksheet(s) to
a temporary (closed) workbook and query the temp workbook.

Here's one I made earlier (you'll need to save *two* worksheets to the
temp workbook):

Option Explicit

Sub Test()

Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim Con As Object
Dim rs As Object
Dim strCon As String
Dim strPath As String
Dim strSql1 As String

' Amend the following constants to suit
Const FILENAME_XL_TEMP As String = "" & _
"delete_me.xls"
Const TABLE_NAME_CURRENT As String = "" & _
"XXX"
Const TABLE_NAME_NEW As String = "" & _
"MyNewTable"

' Do NOT amend the following constants
Const CONN_STRING_1 As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<PATH><FILENAME>;" & _
"Extended Properties='Excel 8.0;HDR=YES'"

' Build connection strings
strPath = ThisWorkbook.Path & _
Application.PathSeparator

strCon = CONN_STRING_1
strCon = Replace(strCon, _
"<PATH>", strPath)
strCon = Replace(strCon, _
"<FILENAME>", FILENAME_XL_TEMP)

' Build sql statement
strSql1 = ""
strSql1 = strSql1 & "SELECT Col1 FROM "
strSql1 = strSql1 & " [" & TABLE_NAME_CURRENT & "$]"
' strSql1 = strSql1 & " WHERE Co2=1 OR Col2=3"

' Delete old instance of temp workbook
On Error Resume Next
Kill strPath & FILENAME_XL_TEMP
On Error GoTo 0

' Save copy of worksheet to temp workbook
Set wb = Excel.Application.Workbooks.Add()
With wb
ThisWorkbook.Worksheets(TABLE_NAME_CURRENT). _
Copy .Worksheets(1)
.SaveAs strPath & FILENAME_XL_TEMP
.Close
End With

' Open connection to temp workbook
Set Con = CreateObject("ADODB.Connection")
With Con
.ConnectionString = strCon
.CursorLocation = 3
.Open
Set rs = .Execute(strSql1)
End With

Sheet1.ListBox1.List = _
Application.Transpose(rs.GetRows())

rs.Close
Con.Close

End Sub

--

 

[L Mehl]

Thanks onedaywhen --

I ended up doing it in the same workbook, populating the listbox when
needed, using code similar to:

blnMatchFound = False

For aLoopDefinition 'loop through imported
'get fileNameImported
blnMatchFound = False
For bLoopDefinition 'loop through plotted
'get fileNamePlotted
If fileNameImported = fileNamePlotted Then 'filenames match
blnMatchFound = True
Exit For
End If
Next bLoopValue '.b.
If blnMatchFound = False Then
'add to listbox
End If
Next aLoopValue '.a.

L Mehl

...

If I could use SQL in this app, the source for the listbox would look
something like:

SELECT name_Imported FROM tblImported
WHERE name_Imported NOT IN (SELECT name_Plotted FROM tblPlotted)

Example:
Imported Plotted
------------ ----------
series1 series1
series2 series4
series3
series4
series5

The listpox would contain the values:
series2
series3
series5

The more usual SQL construct would be to use an OUTER JOIN:

SELECT T2.name_Plotted
FROM [tblImported] T1
LEFT JOIN [tblPlotted] T2
ON T1.name_Imported = T2.name_Plotted
WHERE T2.name_Plotted IS NOT NULL

You can access the data in a workbook using ADO but due to the dreaded
memory leak bug, you can't query an open workbook. Happily, the
workaround is easy enough: save a copy of the relevant worksheet(s) to
a temporary (closed) workbook and query the temp workbook.

Here's one I made earlier (you'll need to save *two* worksheets to the
temp workbook):

Option Explicit

Sub Test()

Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim Con As Object
Dim rs As Object
Dim strCon As String
Dim strPath As String
Dim strSql1 As String

' Amend the following constants to suit
Const FILENAME_XL_TEMP As String = "" & _
"delete_me.xls"
Const TABLE_NAME_CURRENT As String = "" & _
"XXX"
Const TABLE_NAME_NEW As String = "" & _
"MyNewTable"

' Do NOT amend the following constants
Const CONN_STRING_1 As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<PATH><FILENAME>;" & _
"Extended Properties='Excel 8.0;HDR=YES'"

' Build connection strings
strPath = ThisWorkbook.Path & _
Application.PathSeparator

strCon = CONN_STRING_1
strCon = Replace(strCon, _
"<PATH>", strPath)
strCon = Replace(strCon, _
"<FILENAME>", FILENAME_XL_TEMP)

' Build sql statement
strSql1 = ""
strSql1 = strSql1 & "SELECT Col1 FROM "
strSql1 = strSql1 & " [" & TABLE_NAME_CURRENT & "$]"
' strSql1 = strSql1 & " WHERE Co2=1 OR Col2=3"

' Delete old instance of temp workbook
On Error Resume Next
Kill strPath & FILENAME_XL_TEMP
On Error GoTo 0

' Save copy of worksheet to temp workbook
Set wb = Excel.Application.Workbooks.Add()
With wb
ThisWorkbook.Worksheets(TABLE_NAME_CURRENT). _
Copy .Worksheets(1)
.SaveAs strPath & FILENAME_XL_TEMP
.Close
End With

' Open connection to temp workbook
Set Con = CreateObject("ADODB.Connection")
With Con
.ConnectionString = strCon
.CursorLocation = 3
.Open
Set rs = .Execute(strSql1)
End With

Sheet1.ListBox1.List = _
Application.Transpose(rs.GetRows())

rs.Close
Con.Close

End Sub

--

 

[onedaywhen]

...

Thanks onedaywhen --
I ended up doing it in the same workbook, populating the listbox when
needed, using code similar to <snip>

It's good when someone takes the time to post back even if they
haven't taken your advice. So no offence to you but ...

.... I'm finding it increasingly frustrating that when people say, 'If
I could do this with SQL...', and 'I'm only doing this in Excel
because I don't have MS Access...' and I then I demonstrate to them
how to query a workbook using SQL and that one doesn't need the MS
Access application to create, maintain and query Jet ('Access')
databases and one can do all this (and I do!) with VBA code in Excel,
they up not be interested after all. I am so unappreciated, sob sob
(please - send no flowers)

--

 

[L Mehl]

Not intending to add to your frustration ...

I have to confess that my Excel skills are at novice (= inefficient) level
in manipulating ranges/datasets, and other operations necessary for
charting.

I think I would be much more productive keeping the data in a SQL db and
using Excel for charting, and will have to try it once, just to be sure.

Thanks again for the code you suggested.

Larry