Macro to copy from workbooks listed as http links

Hi,

Is it possible to have a macro for this:

1. Master workbook contains a sheet listing a set of sub-workbooks as http
links
2. Open linked sub-workbooks (either all at once or individually)
3. Copy defined range of cells from defined sheet name in opened
sub-workbook to the master workbook (data only, not formatting). (Range and
sheet name is same for all sub-workbooks). Do this for all the linked
workbooks without overwriting any of the previous copied cells.
4. Close sub-workbook(s)

Alternatively, can this be done with the workbooks all stored as files ina
named network directory?

Thanks,

Nick


that will open all workbooks listed on a sheet as http links, copy a
defined set of cell data from one of the sheets into the master workbook, and
then close the workbook.

I presume what you call subworkbooks are worksheets

Regards,
--
AP

"Nick Smith" <NickSmith@discussions.microsoft.com> a écrit dans le message
de news:C0EBADA2-99A3-4F70-A1FD-24B4A362DD59@microsoft.com...
> Hi,
>
> Is it possible to have a macro for this:
>
> 1. Master workbook contains a sheet listing a set of sub-workbooks as
http
> links
> 2. Open linked sub-workbooks (either all at once or individually)
> 3. Copy defined range of cells from defined sheet name in opened
> sub-workbook to the master workbook (data only, not formatting). (Range
and
> sheet name is same for all sub-workbooks). Do this for all the linked
> workbooks without overwriting any of the previous copied cells.
> 4. Close sub-workbook(s)
>
> Alternatively, can this be done with the workbooks all stored as files ina
> named network directory?
>
> Thanks,
>
> Nick
>
>
> that will open all workbooks listed on a sheet as http links, copy a
> defined set of cell data from one of the sheets into the master workbook,
and
> then close the workbook.

What I mean is that I have several excel files (sub-workbooks) with different
file names saved on a network drive. It is the cell data contained in one
sheet of each of these files that I need to import to a master file.

"Ardus Petus" wrote:

> I presume what you call subworkbooks are worksheets
>
> Regards,
> --
> AP
>
> "Nick Smith" <NickSmith@discussions.microsoft.com> a écrit dans le message
> de news:C0EBADA2-99A3-4F70-A1FD-24B4A362DD59@microsoft.com...
> > Hi,
> >
> > Is it possible to have a macro for this:
> >
> > 1. Master workbook contains a sheet listing a set of sub-workbooks as
> http
> > links
> > 2. Open linked sub-workbooks (either all at once or individually)
> > 3. Copy defined range of cells from defined sheet name in opened
> > sub-workbook to the master workbook (data only, not formatting). (Range
> and
> > sheet name is same for all sub-workbooks). Do this for all the linked
> > workbooks without overwriting any of the previous copied cells.
> > 4. Close sub-workbook(s)
> >
> > Alternatively, can this be done with the workbooks all stored as files ina
> > named network directory?
> >
> > Thanks,
> >
> > Nick
> >
> >
> > that will open all workbooks listed on a sheet as http links, copy a
> > defined set of cell data from one of the sheets into the master workbook,
> and
> > then close the workbook.
>
>
>

assume they are all stored in a single folder and you want to process all
files in that folder. the master workbook is not in that folder. Master
workbook contains the code and data is copied to the first sheet in the tab
order, starting in the next available cell in column A. Data to be copied
from each workbook is in the first sheet in the tab order in cells A1:F20

Sub GetData()
Dim sPath as String, sName as String
Dim rng as Range, bk as Workbook
sPath = "C:\Myfolder\"
sname = dir(sPath & "*.xls")
do while sName <> ""
set rng = thisworkbook.worksheets(1).Cells(rows.count,1).End(xlup)(2)
set bk = workbooks.Open(sPath & sname)
bk.worksheets(1).Range("A1:F20").copy rng
bk.close Savechanges:=False
sName = dir()
Loop
End Sub


Adjust to suit your actual situation.
--
Regards,
Tom Ogilvy


"Nick Smith" wrote:

> What I mean is that I have several excel files (sub-workbooks) with different
> file names saved on a network drive. It is the cell data contained in one
> sheet of each of these files that I need to import to a master file.
>
> "Ardus Petus" wrote:
>
> > I presume what you call subworkbooks are worksheets
> >
> > Regards,
> > --
> > AP
> >
> > "Nick Smith" <NickSmith@discussions.microsoft.com> a écrit dans le message
> > de news:C0EBADA2-99A3-4F70-A1FD-24B4A362DD59@microsoft.com...
> > > Hi,
> > >
> > > Is it possible to have a macro for this:
> > >
> > > 1. Master workbook contains a sheet listing a set of sub-workbooks as
> > http
> > > links
> > > 2. Open linked sub-workbooks (either all at once or individually)
> > > 3. Copy defined range of cells from defined sheet name in opened
> > > sub-workbook to the master workbook (data only, not formatting). (Range
> > and
> > > sheet name is same for all sub-workbooks). Do this for all the linked
> > > workbooks without overwriting any of the previous copied cells.
> > > 4. Close sub-workbook(s)
> > >
> > > Alternatively, can this be done with the workbooks all stored as files ina
> > > named network directory?
> > >
> > > Thanks,
> > >
> > > Nick
> > >
> > >
> > > that will open all workbooks listed on a sheet as http links, copy a
> > > defined set of cell data from one of the sheets into the master workbook,
> > and
> > > then close the workbook.
> >
> >
> >

Thanks Tom. So I can amend it for my particular needs, can you please tell
me what the different parts of this line do?

Set rng = ThisWorkbook.Worksheets(1).Cells(Rows.Count, 1).End(xlUp)(2)

I presume I can change the first (1) to ("Sheet Name") to go to the sheet I
want, but what do the next 2 expressions do exactly? I want them to go to
the next empty cell in column A after cell A14 for the pasting action. Not
familiar with the End(xlUp)(2) expression at all.

Thanks in advance,

Nick


"Tom Ogilvy" wrote:

> assume they are all stored in a single folder and you want to process all
> files in that folder. the master workbook is not in that folder. Master
> workbook contains the code and data is copied to the first sheet in the tab
> order, starting in the next available cell in column A. Data to be copied
> from each workbook is in the first sheet in the tab order in cells A1:F20
>
> Sub GetData()
> Dim sPath as String, sName as String
> Dim rng as Range, bk as Workbook
> sPath = "C:\Myfolder\"
> sname = dir(sPath & "*.xls")
> do while sName <> ""
> set rng = thisworkbook.worksheets(1).Cells(rows.count,1).End(xlup)(2)
> set bk = workbooks.Open(sPath & sname)
> bk.worksheets(1).Range("A1:F20").copy rng
> bk.close Savechanges:=False
> sName = dir()
> Loop
> End Sub
>
>
> Adjust to suit your actual situation.
> --
> Regards,
> Tom Ogilvy
>
>
> "Nick Smith" wrote:
>
> > What I mean is that I have several excel files (sub-workbooks) with different
> > file names saved on a network drive. It is the cell data contained in one
> > sheet of each of these files that I need to import to a master file.
> >
> > "Ardus Petus" wrote:
> >
> > > I presume what you call subworkbooks are worksheets
> > >
> > > Regards,
> > > --
> > > AP
> > >
> > > "Nick Smith" <NickSmith@discussions.microsoft.com> a écrit dans le message
> > > de news:C0EBADA2-99A3-4F70-A1FD-24B4A362DD59@microsoft.com...
> > > > Hi,
> > > >
> > > > Is it possible to have a macro for this:
> > > >
> > > > 1. Master workbook contains a sheet listing a set of sub-workbooks as
> > > http
> > > > links
> > > > 2. Open linked sub-workbooks (either all at once or individually)
> > > > 3. Copy defined range of cells from defined sheet name in opened
> > > > sub-workbook to the master workbook (data only, not formatting). (Range
> > > and
> > > > sheet name is same for all sub-workbooks). Do this for all the linked
> > > > workbooks without overwriting any of the previous copied cells.
> > > > 4. Close sub-workbook(s)
> > > >
> > > > Alternatively, can this be done with the workbooks all stored as files ina
> > > > named network directory?
> > > >
> > > > Thanks,
> > > >
> > > > Nick
> > > >
> > > >
> > > > that will open all workbooks listed on a sheet as http links, copy a
> > > > defined set of cell data from one of the sheets into the master workbook,
> > > and
> > > > then close the workbook.
> > >
> > >
> > >

That line fines the next empty cell in coulmn A.

in a new worksheet, go to A14 and enter the number 10. Now hit the end key,
then the down arrow. That takes you to the bottom of the sheet. This is
where that line of code start. Now hit End and then the up arrow. You
should now be back at A14. That is what the End(xlup) does.
Cells(rows.count,1) specifies to start in A65536. the (2) on the end
means go to the next cell down. So it would put you on A15 (or rather give
you a reference to A15).

Yes, you can replace the 1 with the sheet name.

to start in A14

Sub GetData()
Dim sPath as String, sName as String
Dim rng as Range, bk as Workbook
Dim vA as Variant

vA = "Sheet1"

sPath = "C:\Myfolder\"
sname = dir(sPath & "*.xls")
do while sName <> ""
if isempty(thisworkbook.worksheets(vA).Range("A14")) then
set rng = Thisworkbook.Worksheets(vA).Range("A14")
else
set rng = thisworkbook.worksheets(vA) _
.Cells(rows.count,1).End(xlup)(2)
End if
set bk = workbooks.Open(sPath & sname)
bk.worksheets(1).Range("A1:F20").copy rng
bk.close Savechanges:=False
sName = dir()
Loop
End Sub


--
Regards,
Tom Ogilvy


"Nick Smith" <NickSmith@discussions.microsoft.com> wrote in message
news:8E3A92F6-C9AD-4968-8B4E-4FFFC6F3E3DD@microsoft.com...
> Thanks Tom. So I can amend it for my particular needs, can you please
tell
> me what the different parts of this line do?
>
> Set rng = ThisWorkbook.Worksheets(1).Cells(Rows.Count, 1).End(xlUp)(2)
>
> I presume I can change the first (1) to ("Sheet Name") to go to the sheet
I
> want, but what do the next 2 expressions do exactly? I want them to go to
> the next empty cell in column A after cell A14 for the pasting action.
Not
> familiar with the End(xlUp)(2) expression at all.
>
> Thanks in advance,
>
> Nick
>
>
> "Tom Ogilvy" wrote:
>
> > assume they are all stored in a single folder and you want to process
all
> > files in that folder. the master workbook is not in that folder.
Master
> > workbook contains the code and data is copied to the first sheet in the
tab
> > order, starting in the next available cell in column A. Data to be
copied
> > from each workbook is in the first sheet in the tab order in cells
A1:F20
> >
> > Sub GetData()
> > Dim sPath as String, sName as String
> > Dim rng as Range, bk as Workbook
> > sPath = "C:\Myfolder\"
> > sname = dir(sPath & "*.xls")
> > do while sName <> ""
> > set rng = thisworkbook.worksheets(1).Cells(rows.count,1).End(xlup)(2)
> > set bk = workbooks.Open(sPath & sname)
> > bk.worksheets(1).Range("A1:F20").copy rng
> > bk.close Savechanges:=False
> > sName = dir()
> > Loop
> > End Sub
> >
> >
> > Adjust to suit your actual situation.
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Nick Smith" wrote:
> >
> > > What I mean is that I have several excel files (sub-workbooks) with
different
> > > file names saved on a network drive. It is the cell data contained in
one
> > > sheet of each of these files that I need to import to a master file.
> > >
> > > "Ardus Petus" wrote:
> > >
> > > > I presume what you call subworkbooks are worksheets
> > > >
> > > > Regards,
> > > > --
> > > > AP
> > > >
> > > > "Nick Smith" <NickSmith@discussions.microsoft.com> a écrit dans le
message
> > > > de news:C0EBADA2-99A3-4F70-A1FD-24B4A362DD59@microsoft.com...
> > > > > Hi,
> > > > >
> > > > > Is it possible to have a macro for this:
> > > > >
> > > > > 1. Master workbook contains a sheet listing a set of
sub-workbooks as
> > > > http
> > > > > links
> > > > > 2. Open linked sub-workbooks (either all at once or individually)
> > > > > 3. Copy defined range of cells from defined sheet name in opened
> > > > > sub-workbook to the master workbook (data only, not formatting).
(Range
> > > > and
> > > > > sheet name is same for all sub-workbooks). Do this for all the
linked
> > > > > workbooks without overwriting any of the previous copied cells.
> > > > > 4. Close sub-workbook(s)
> > > > >
> > > > > Alternatively, can this be done with the workbooks all stored as
files ina
> > > > > named network directory?
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Nick
> > > > >
> > > > >
> > > > > that will open all workbooks listed on a sheet as http links,
copy a
> > > > > defined set of cell data from one of the sheets into the master
workbook,
> > > > and
> > > > > then close the workbook.
> > > >
> > > >
> > > >