Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 87
Default Find matching tab names between different workbooks

I have a workbook (lets call it Filter.xls) containing several tabs with
different names, lets say: 111111, 222222, and 333333. The Filter.xls file
contains formulas which are applied to records of numerical data obtained
from separate works (lets call them Data1.xls, Data2.xls, Data3.xls, etc.)
containing several tabs with different names like 111111, xxxx, 222222, xxxx,
333333, xxxx. All aforementioned files would be in the same working folder.

What Id like the VBA code to do is to cycle through any existing
Data[#].xls files and check if there are matching tab names between them and
the Filter.xls file and, if so, copy the record set from the matching tab in
the Data[#].xls file to the matching tab in the Filter.xls file. I already
have code to copy the record set from the Data[#].xls file into the
Filter.xls file but what I am missing is the code that determines if there is
a match between the tabs in the Filter.xls file and any available Data[#].xls
file. If there is no match then notify the user and exit.

Id appreciate anyones assistance. Please contact me if you need further
details.

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6,953
Default Find matching tab names between different workbooks

the following untested pseudo code should get you started:
Sub OpenFiles()
Dim sh as Worksheet, sh1 as Worksheet
dim bk as Workbook
Dim sName as String
Dim sPath as String
Dim bFound as Boolean
sPath = "C:\Myfiles\"
sName = Dir(sPath & "Data*.xls")
do while sName < ""
bFound = False
set bk = workbooks.open( sPath & sName)
for each sh in bk.worksheets
set sh1 = Nothing
On error resume Next
set sh1 = workbooks("Filter.xls").Worksheets(sh.name)
On Error goto 0
if not sh1 is nothing then
' copy from sh to sh1
bFound = True
else
' matching sheet not found
end if
Next sh
If not bFound then
msgbox "No match found in " & bk.name
end if
bk.close Savechanges:=False
sName = dir()
Loop
End Sub



--
Regards,
Tom Ogilvy


"Freddy" wrote:

I have a workbook (lets call it Filter.xls) containing several tabs with
different names, lets say: 111111, 222222, and 333333. The Filter.xls file
contains formulas which are applied to records of numerical data obtained
from separate works (lets call them Data1.xls, Data2.xls, Data3.xls, etc.)
containing several tabs with different names like 111111, xxxx, 222222, xxxx,
333333, xxxx. All aforementioned files would be in the same working folder.

What Id like the VBA code to do is to cycle through any existing
Data[#].xls files and check if there are matching tab names between them and
the Filter.xls file and, if so, copy the record set from the matching tab in
the Data[#].xls file to the matching tab in the Filter.xls file. I already
have code to copy the record set from the Data[#].xls file into the
Filter.xls file but what I am missing is the code that determines if there is
a match between the tabs in the Filter.xls file and any available Data[#].xls
file. If there is no match then notify the user and exit.

Id appreciate anyones assistance. Please contact me if you need further
details.

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 87
Default Find matching tab names between different workbooks

I will test your suggested code and advise.

"Tom Ogilvy" wrote:

the following untested pseudo code should get you started:
Sub OpenFiles()
Dim sh as Worksheet, sh1 as Worksheet
dim bk as Workbook
Dim sName as String
Dim sPath as String
Dim bFound as Boolean
sPath = "C:\Myfiles\"
sName = Dir(sPath & "Data*.xls")
do while sName < ""
bFound = False
set bk = workbooks.open( sPath & sName)
for each sh in bk.worksheets
set sh1 = Nothing
On error resume Next
set sh1 = workbooks("Filter.xls").Worksheets(sh.name)
On Error goto 0
if not sh1 is nothing then
' copy from sh to sh1
bFound = True
else
' matching sheet not found
end if
Next sh
If not bFound then
msgbox "No match found in " & bk.name
end if
bk.close Savechanges:=False
sName = dir()
Loop
End Sub



--
Regards,
Tom Ogilvy


"Freddy" wrote:

I have a workbook (lets call it Filter.xls) containing several tabs with
different names, lets say: 111111, 222222, and 333333. The Filter.xls file
contains formulas which are applied to records of numerical data obtained
from separate works (lets call them Data1.xls, Data2.xls, Data3.xls, etc.)
containing several tabs with different names like 111111, xxxx, 222222, xxxx,
333333, xxxx. All aforementioned files would be in the same working folder.

What Id like the VBA code to do is to cycle through any existing
Data[#].xls files and check if there are matching tab names between them and
the Filter.xls file and, if so, copy the record set from the matching tab in
the Data[#].xls file to the matching tab in the Filter.xls file. I already
have code to copy the record set from the Data[#].xls file into the
Filter.xls file but what I am missing is the code that determines if there is
a match between the tabs in the Filter.xls file and any available Data[#].xls
file. If there is no match then notify the user and exit.

Id appreciate anyones assistance. Please contact me if you need further
details.

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 87
Default Find matching tab names between different workbooks

Tom, knowing that you based your sample code on the information I supplied, a
new item has come up. I need to store the 6 leftmost characters of the tab
name in the Data.xls files and use that as a basis to look in the Filter.xls
file for a matching tab name. Also, I did not detect in your sample code how
the tab names in the Filter.xls file were being evaluated. Please review and
advise.

"Tom Ogilvy" wrote:

the following untested pseudo code should get you started:
Sub OpenFiles()
Dim sh as Worksheet, sh1 as Worksheet
dim bk as Workbook
Dim sName as String
Dim sPath as String
Dim bFound as Boolean
sPath = "C:\Myfiles\"
sName = Dir(sPath & "Data*.xls")
do while sName < ""
bFound = False
set bk = workbooks.open( sPath & sName)
for each sh in bk.worksheets
set sh1 = Nothing
On error resume Next
set sh1 = workbooks("Filter.xls").Worksheets(sh.name)
On Error goto 0
if not sh1 is nothing then
' copy from sh to sh1
bFound = True
else
' matching sheet not found
end if
Next sh
If not bFound then
msgbox "No match found in " & bk.name
end if
bk.close Savechanges:=False
sName = dir()
Loop
End Sub



--
Regards,
Tom Ogilvy


"Freddy" wrote:

I have a workbook (lets call it Filter.xls) containing several tabs with
different names, lets say: 111111, 222222, and 333333. The Filter.xls file
contains formulas which are applied to records of numerical data obtained
from separate works (lets call them Data1.xls, Data2.xls, Data3.xls, etc.)
containing several tabs with different names like 111111, xxxx, 222222, xxxx,
333333, xxxx. All aforementioned files would be in the same working folder.

What Id like the VBA code to do is to cycle through any existing
Data[#].xls files and check if there are matching tab names between them and
the Filter.xls file and, if so, copy the record set from the matching tab in
the Data[#].xls file to the matching tab in the Filter.xls file. I already
have code to copy the record set from the Data[#].xls file into the
Filter.xls file but what I am missing is the code that determines if there is
a match between the tabs in the Filter.xls file and any available Data[#].xls
file. If there is no match then notify the user and exit.

Id appreciate anyones assistance. Please contact me if you need further
details.

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6,953
Default Find matching tab names between different workbooks

I check for the tab name he (I try to set a reference to the sheet - if
unsuccessful, then I know the sheet doesn't exist).

for each sh in bk.worksheets
set sh1 = Nothing
On error resume Next
set sh1 = workbooks("Filter.xls").Worksheets(sh.name)
On Error goto 0
if not sh1 is nothing then
' copy from sh to sh1
bFound = True
else
' matching sheet not found
end if

if the Filter.xls actually has names of 6 characters, then you could modify

set sh1 = workbooks("Filter.xls").Worksheets(sh.name)

to

set sh1 = workbooks("Filter.xls").Worksheets(left(sh.name,6) )

if you are looking for a match on the first 6 characters and both names are
longer than six characters, then you would have to loop through the names.

--
Regards,
Tom Ogilvy



"Freddy" wrote:

Tom, knowing that you based your sample code on the information I supplied, a
new item has come up. I need to store the 6 leftmost characters of the tab
name in the Data.xls files and use that as a basis to look in the Filter.xls
file for a matching tab name. Also, I did not detect in your sample code how
the tab names in the Filter.xls file were being evaluated. Please review and
advise.

"Tom Ogilvy" wrote:

the following untested pseudo code should get you started:
Sub OpenFiles()
Dim sh as Worksheet, sh1 as Worksheet
dim bk as Workbook
Dim sName as String
Dim sPath as String
Dim bFound as Boolean
sPath = "C:\Myfiles\"
sName = Dir(sPath & "Data*.xls")
do while sName < ""
bFound = False
set bk = workbooks.open( sPath & sName)
for each sh in bk.worksheets
set sh1 = Nothing
On error resume Next
set sh1 = workbooks("Filter.xls").Worksheets(sh.name)
On Error goto 0
if not sh1 is nothing then
' copy from sh to sh1
bFound = True
else
' matching sheet not found
end if
Next sh
If not bFound then
msgbox "No match found in " & bk.name
end if
bk.close Savechanges:=False
sName = dir()
Loop
End Sub



--
Regards,
Tom Ogilvy


"Freddy" wrote:

I have a workbook (lets call it Filter.xls) containing several tabs with
different names, lets say: 111111, 222222, and 333333. The Filter.xls file
contains formulas which are applied to records of numerical data obtained
from separate works (lets call them Data1.xls, Data2.xls, Data3.xls, etc.)
containing several tabs with different names like 111111, xxxx, 222222, xxxx,
333333, xxxx. All aforementioned files would be in the same working folder.

What Id like the VBA code to do is to cycle through any existing
Data[#].xls files and check if there are matching tab names between them and
the Filter.xls file and, if so, copy the record set from the matching tab in
the Data[#].xls file to the matching tab in the Filter.xls file. I already
have code to copy the record set from the Data[#].xls file into the
Filter.xls file but what I am missing is the code that determines if there is
a match between the tabs in the Filter.xls file and any available Data[#].xls
file. If there is no match then notify the user and exit.

Id appreciate anyones assistance. Please contact me if you need further
details.



  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 87
Default Find matching tab names between different workbooks

I will continue testing and advise.

"Tom Ogilvy" wrote:

I check for the tab name he (I try to set a reference to the sheet - if
unsuccessful, then I know the sheet doesn't exist).

for each sh in bk.worksheets
set sh1 = Nothing
On error resume Next
set sh1 = workbooks("Filter.xls").Worksheets(sh.name)
On Error goto 0
if not sh1 is nothing then
' copy from sh to sh1
bFound = True
else
' matching sheet not found
end if

if the Filter.xls actually has names of 6 characters, then you could modify

set sh1 = workbooks("Filter.xls").Worksheets(sh.name)

to

set sh1 = workbooks("Filter.xls").Worksheets(left(sh.name,6) )

if you are looking for a match on the first 6 characters and both names are
longer than six characters, then you would have to loop through the names.

--
Regards,
Tom Ogilvy



"Freddy" wrote:

Tom, knowing that you based your sample code on the information I supplied, a
new item has come up. I need to store the 6 leftmost characters of the tab
name in the Data.xls files and use that as a basis to look in the Filter.xls
file for a matching tab name. Also, I did not detect in your sample code how
the tab names in the Filter.xls file were being evaluated. Please review and
advise.

"Tom Ogilvy" wrote:

the following untested pseudo code should get you started:
Sub OpenFiles()
Dim sh as Worksheet, sh1 as Worksheet
dim bk as Workbook
Dim sName as String
Dim sPath as String
Dim bFound as Boolean
sPath = "C:\Myfiles\"
sName = Dir(sPath & "Data*.xls")
do while sName < ""
bFound = False
set bk = workbooks.open( sPath & sName)
for each sh in bk.worksheets
set sh1 = Nothing
On error resume Next
set sh1 = workbooks("Filter.xls").Worksheets(sh.name)
On Error goto 0
if not sh1 is nothing then
' copy from sh to sh1
bFound = True
else
' matching sheet not found
end if
Next sh
If not bFound then
msgbox "No match found in " & bk.name
end if
bk.close Savechanges:=False
sName = dir()
Loop
End Sub



--
Regards,
Tom Ogilvy


"Freddy" wrote:

I have a workbook (lets call it Filter.xls) containing several tabs with
different names, lets say: 111111, 222222, and 333333. The Filter.xls file
contains formulas which are applied to records of numerical data obtained
from separate works (lets call them Data1.xls, Data2.xls, Data3.xls, etc.)
containing several tabs with different names like 111111, xxxx, 222222, xxxx,
333333, xxxx. All aforementioned files would be in the same working folder.

What Id like the VBA code to do is to cycle through any existing
Data[#].xls files and check if there are matching tab names between them and
the Filter.xls file and, if so, copy the record set from the matching tab in
the Data[#].xls file to the matching tab in the Filter.xls file. I already
have code to copy the record set from the Data[#].xls file into the
Filter.xls file but what I am missing is the code that determines if there is
a match between the tabs in the Filter.xls file and any available Data[#].xls
file. If there is no match then notify the user and exit.

Id appreciate anyones assistance. Please contact me if you need further
details.

  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 87
Default Find matching tab names between different workbooks

By the way, the name of the Filter.xls file can vary depending on a company
name. Can that be incorporated in your sample code? The latter portion of the
file name will always have _Filter.xls. An example name is
"Company1_Filter.xls".

"Tom Ogilvy" wrote:

I check for the tab name he (I try to set a reference to the sheet - if
unsuccessful, then I know the sheet doesn't exist).

for each sh in bk.worksheets
set sh1 = Nothing
On error resume Next
set sh1 = workbooks("Filter.xls").Worksheets(sh.name)
On Error goto 0
if not sh1 is nothing then
' copy from sh to sh1
bFound = True
else
' matching sheet not found
end if

if the Filter.xls actually has names of 6 characters, then you could modify

set sh1 = workbooks("Filter.xls").Worksheets(sh.name)

to

set sh1 = workbooks("Filter.xls").Worksheets(left(sh.name,6) )

if you are looking for a match on the first 6 characters and both names are
longer than six characters, then you would have to loop through the names.

--
Regards,
Tom Ogilvy



"Freddy" wrote:

Tom, knowing that you based your sample code on the information I supplied, a
new item has come up. I need to store the 6 leftmost characters of the tab
name in the Data.xls files and use that as a basis to look in the Filter.xls
file for a matching tab name. Also, I did not detect in your sample code how
the tab names in the Filter.xls file were being evaluated. Please review and
advise.

"Tom Ogilvy" wrote:

the following untested pseudo code should get you started:
Sub OpenFiles()
Dim sh as Worksheet, sh1 as Worksheet
dim bk as Workbook
Dim sName as String
Dim sPath as String
Dim bFound as Boolean
sPath = "C:\Myfiles\"
sName = Dir(sPath & "Data*.xls")
do while sName < ""
bFound = False
set bk = workbooks.open( sPath & sName)
for each sh in bk.worksheets
set sh1 = Nothing
On error resume Next
set sh1 = workbooks("Filter.xls").Worksheets(sh.name)
On Error goto 0
if not sh1 is nothing then
' copy from sh to sh1
bFound = True
else
' matching sheet not found
end if
Next sh
If not bFound then
msgbox "No match found in " & bk.name
end if
bk.close Savechanges:=False
sName = dir()
Loop
End Sub



--
Regards,
Tom Ogilvy


"Freddy" wrote:

I have a workbook (lets call it Filter.xls) containing several tabs with
different names, lets say: 111111, 222222, and 333333. The Filter.xls file
contains formulas which are applied to records of numerical data obtained
from separate works (lets call them Data1.xls, Data2.xls, Data3.xls, etc.)
containing several tabs with different names like 111111, xxxx, 222222, xxxx,
333333, xxxx. All aforementioned files would be in the same working folder.

What Id like the VBA code to do is to cycle through any existing
Data[#].xls files and check if there are matching tab names between them and
the Filter.xls file and, if so, copy the record set from the matching tab in
the Data[#].xls file to the matching tab in the Filter.xls file. I already
have code to copy the record set from the Data[#].xls file into the
Filter.xls file but what I am missing is the code that determines if there is
a match between the tabs in the Filter.xls file and any available Data[#].xls
file. If there is no match then notify the user and exit.

Id appreciate anyones assistance. Please contact me if you need further
details.

  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6,953
Default Find matching tab names between different workbooks

Sub OpenFiles()
Dim sh as Worksheet, sh1 as Worksheet
dim bk as Workbook, bk1 as workbook
Dim bk2 as Workbook
Dim sName as String
Dim sPath as String
Dim bFound as Boolean
for each bk1 in workbooks
if instr(1,bk1.name,"filter.xls",vbTextcompare) then
set bk2 = bk1
exit for
end if
Next
if bk2 is nothing then
Msgbox "Filter workbook not found, exiting"
exit sub
end if

sPath = "C:\Myfiles\"
sName = Dir(sPath & "Data*.xls")
do while sName < ""
bFound = False
set bk = workbooks.open( sPath & sName)
for each sh in bk.worksheets
set sh1 = Nothing
On error resume Next
set sh1 = bk2.Worksheets(left(sh.name,6))
On Error goto 0
if not sh1 is nothing then
' copy from sh to sh1
bFound = True
else
' matching sheet not found
end if
Next sh
If not bFound then
msgbox "No match found in " & bk.name
end if
bk.close Savechanges:=False
sName = dir()
Loop
End Sub

--
Regards,
Tom Ogilvy


"Freddy" wrote:

By the way, the name of the Filter.xls file can vary depending on a company
name. Can that be incorporated in your sample code? The latter portion of the
file name will always have _Filter.xls. An example name is
"Company1_Filter.xls".

"Tom Ogilvy" wrote:

I check for the tab name he (I try to set a reference to the sheet - if
unsuccessful, then I know the sheet doesn't exist).

for each sh in bk.worksheets
set sh1 = Nothing
On error resume Next
set sh1 = workbooks("Filter.xls").Worksheets(sh.name)
On Error goto 0
if not sh1 is nothing then
' copy from sh to sh1
bFound = True
else
' matching sheet not found
end if

if the Filter.xls actually has names of 6 characters, then you could modify

set sh1 = workbooks("Filter.xls").Worksheets(sh.name)

to

set sh1 = workbooks("Filter.xls").Worksheets(left(sh.name,6) )

if you are looking for a match on the first 6 characters and both names are
longer than six characters, then you would have to loop through the names.

--
Regards,
Tom Ogilvy



"Freddy" wrote:

Tom, knowing that you based your sample code on the information I supplied, a
new item has come up. I need to store the 6 leftmost characters of the tab
name in the Data.xls files and use that as a basis to look in the Filter.xls
file for a matching tab name. Also, I did not detect in your sample code how
the tab names in the Filter.xls file were being evaluated. Please review and
advise.

"Tom Ogilvy" wrote:

the following untested pseudo code should get you started:
Sub OpenFiles()
Dim sh as Worksheet, sh1 as Worksheet
dim bk as Workbook
Dim sName as String
Dim sPath as String
Dim bFound as Boolean
sPath = "C:\Myfiles\"
sName = Dir(sPath & "Data*.xls")
do while sName < ""
bFound = False
set bk = workbooks.open( sPath & sName)
for each sh in bk.worksheets
set sh1 = Nothing
On error resume Next
set sh1 = workbooks("Filter.xls").Worksheets(sh.name)
On Error goto 0
if not sh1 is nothing then
' copy from sh to sh1
bFound = True
else
' matching sheet not found
end if
Next sh
If not bFound then
msgbox "No match found in " & bk.name
end if
bk.close Savechanges:=False
sName = dir()
Loop
End Sub



--
Regards,
Tom Ogilvy


"Freddy" wrote:

I have a workbook (lets call it Filter.xls) containing several tabs with
different names, lets say: 111111, 222222, and 333333. The Filter.xls file
contains formulas which are applied to records of numerical data obtained
from separate works (lets call them Data1.xls, Data2.xls, Data3.xls, etc.)
containing several tabs with different names like 111111, xxxx, 222222, xxxx,
333333, xxxx. All aforementioned files would be in the same working folder.

What Id like the VBA code to do is to cycle through any existing
Data[#].xls files and check if there are matching tab names between them and
the Filter.xls file and, if so, copy the record set from the matching tab in
the Data[#].xls file to the matching tab in the Filter.xls file. I already
have code to copy the record set from the Data[#].xls file into the
Filter.xls file but what I am missing is the code that determines if there is
a match between the tabs in the Filter.xls file and any available Data[#].xls
file. If there is no match then notify the user and exit.

Id appreciate anyones assistance. Please contact me if you need further
details.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find matching data in two workbooks franklinbukoski Excel Worksheet Functions 1 May 5th 08 10:08 PM
Sorting and matching rows of names with Socials with master list and eliminating the extra names Giacomo Excel Worksheet Functions 1 March 10th 07 01:52 AM
Find names of all open workbooks Graham Whitehead Excel Programming 2 November 15th 06 02:39 PM
Conditional Format - 2 lists of names to find matching cells. Robin Excel Worksheet Functions 2 March 27th 06 05:49 PM
Matching Names in two different workbooks Angela Excel Discussion (Misc queries) 2 December 14th 05 03:26 PM


All times are GMT +1. The time now is 02:52 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"