Using lookup to copy values from a master to client worksheet

M

Malcolm Hind

If I have a columns of data in a spreadsheet with, say, a name in the fist
column, is it possible to create 'client' spreadsheets which contain only
rows with a particular name ? So the 'Master' sheet has many rows for each
name but the 'client' sheet has only entries for a particular name ?

Any help or pointers much appreciated.

Thanks
 
K

Ken Frost

Am I right in thinking this would just allow me to manually select and
copy them ? I would like it to be automatic if that were possible. Thanks
very much for the reply.

Ken
 
F

Frank Kabel

Hi
this is possible with formulas but if your master sheet has a lot of
rows this would slow your file down. Excel is not designed for this
kind of database application :)
 
D

Dave Peterson

You could automate the Advanced filter.

Debra Dalgleish has some sample workbooks at:
http://www.contextures.com/excelfiles.html

Look for:

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- creates a list of unique items,
creates a sheet for each item, then replaces old data with current.
AdvFilterCity.xls 46 kb

and

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
J

Jamie Collins

Frank Kabel said:
Excel is not designed for this
kind of database application :)
Click to expand...

Questionable, to say the least. One may use ADO to access databases.
The OLE DB provider for Jet has been given the capability to query
Excel data. There are article on MSDN telling how to query Excel data
using ADO and OLEDB. If Frank Kabel is not designed for this
kind of application, just say so ;-)

Option Explicit

Sub test()
CopyToNewWorksheet "Master", "client"
End Sub

Private Function CopyToNewWorksheet( _
ByVal SheetName As String, _
Optional ByVal NewSheetName As String _
) As Boolean

Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim Target As Excel.Range
Dim Con As Object
Dim rs As Object
Dim strCon As String
Dim strPath As String
Dim strSql1 As String
Dim lngCounter As Long

' Review the following constant:
Const FILENAME_XL_TEMP As String = "" & _
"delete_me.xls"

Const TABLE_XL_TEMP As String = "" & _
"test_only"

Const SQL As String = "" & _
"SELECT * FROM [<SHEET_NAME>$] WHERE MyName='Hind';"

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

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

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

' Build sql statement
strSql1 = SQL
strSql1 = Replace(strSql1, _
"<SHEET_NAME>", TABLE_XL_TEMP)

' 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(SheetName). _
Copy .Worksheets(1)
.Worksheets(1).Name = TABLE_XL_TEMP
.SaveAs strPath & FILENAME_XL_TEMP
.Close
End With

' Open connection to temp workbook
Set Con = CreateObject("ADODB.Connection")
With Con
.ConnectionString = strCon
.Open

Set rs = .Execute(strSql1)
End With

Set ws = ThisWorkbook.Worksheets.Add
With ws
If Len(NewSheetName) > 0 Then
.Name = NewSheetName
End If
Set Target = .Range("A1")
End With

With rs
For lngCounter = 1 To .fields.Count
Target(1, lngCounter).Value = _
.fields(lngCounter - 1).Name
Next
End With

Target(2, 1).CopyFromRecordset rs

Con.Close

CopyToNewWorksheet = True

End Function


Jamie.

--
 
F

Frank Kabel

Hi Jamie
:)
Agree with you that one could use ADO for this. But I personally think
Excel should not be used for database like application as it lacks a
lot of standard DB functionality. ;-)

--
Regards
Frank Kabel
Frankfurt, Germany


Jamie said:
Frank Kabel said:
Excel is not designed for this
kind of database application :)
Click to expand...

Questionable, to say the least. One may use ADO to access databases.
The OLE DB provider for Jet has been given the capability to query
Excel data. There are article on MSDN telling how to query Excel data
using ADO and OLEDB. If Frank Kabel is not designed for this
kind of application, just say so ;-)

Option Explicit

Sub test()
CopyToNewWorksheet "Master", "client"
End Sub

Private Function CopyToNewWorksheet( _
ByVal SheetName As String, _
Optional ByVal NewSheetName As String _
) As Boolean

Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim Target As Excel.Range
Dim Con As Object
Dim rs As Object
Dim strCon As String
Dim strPath As String
Dim strSql1 As String
Dim lngCounter As Long

' Review the following constant:
Const FILENAME_XL_TEMP As String = "" & _
"delete_me.xls"

Const TABLE_XL_TEMP As String = "" & _
"test_only"

Const SQL As String = "" & _
"SELECT * FROM [<SHEET_NAME>$] WHERE MyName='Hind';"

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

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

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

' Build sql statement
strSql1 = SQL
strSql1 = Replace(strSql1, _
"<SHEET_NAME>", TABLE_XL_TEMP)

' 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(SheetName). _
Copy .Worksheets(1)
.Worksheets(1).Name = TABLE_XL_TEMP
.SaveAs strPath & FILENAME_XL_TEMP
.Close
End With

' Open connection to temp workbook
Set Con = CreateObject("ADODB.Connection")
With Con
.ConnectionString = strCon
.Open

Set rs = .Execute(strSql1)
End With

Set ws = ThisWorkbook.Worksheets.Add
With ws
If Len(NewSheetName) > 0 Then
.Name = NewSheetName
End If
Set Target = .Range("A1")
End With

With rs
For lngCounter = 1 To .fields.Count
Target(1, lngCounter).Value = _
.fields(lngCounter - 1).Name
Next
End With

Target(2, 1).CopyFromRecordset rs

Con.Close

CopyToNewWorksheet = True

End Function


Jamie.
Click to expand...
 
J

Jamie Collins

Frank Kabel said:
Hi Jamie
:)
Agree with you that one could use ADO for this. But I personally think
Excel should not be used for database like application as it lacks a
lot of standard DB functionality. ;-)
Click to expand...

:) Of course: Excel has only approximations to data typing,
constraints, DRI, multi-user, etc. However, in terms of *querying*
existing Excel data, using sql often makes a lot more sense than the
alternatives (Autofilter, VLOOKUP, SUMPRODUCT, array formulas, VBA to
loop through cells, etc).

Jamie.

--
 

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

Need help linking spreadsheets and displaying only populated rows 1
Lookups - how to obtain data from a master spreadsheet into another 5
Referencing Worksheets in separate Workbooks 5
Lookup on worksheet 1
macro to copy formats 1
Referencing Multiple entries in a worksheet 1
How do I sum numbers based on other data 6
vlookup for client list 7

Top