Reference filename used in code from cell

[shamble]

Hi all

I am using the code below to update a spreadsheet with information from
another. What I would like to know is the line with the <<<<<<< is
there any way to reference that filename from a cell instead of
specifying it in the code. For example the filename would be contained
in a sheet called 'M' in cell 'A1'. That way we could enter the name of
the file we want to update into a cell to save messing with the code at
a future date. Thanks in advance

Sheets("ASC").Select
ChDir "\\w2k6001\shared\csdgapp\miteam\Manpower"
Workbooks.Open Filename:= _
"\\w2k6001\shared\CSDGAPP\miTeam\Manpower\AManpowerhcv0.2.xls"
Sheets("ASC").Select
Range("D7").Select
ActiveWindow.TabRatio = 0.943
ActiveWindow.SmallScroll ToRight:=5
ActiveWindow.SmallScroll Down:=69
Range("D7:Q106").Select
Selection.Copy
Windows("060313_hc.xls").Activate <<<<<<<<<<<
Range("D7").Select
ActiveSheet.Paste

 

[Tom Ogilvy]

You can do that, but you would need to be more specific where sheet M is. If
it is in the workbook containing the code, you could change
Windows("060313_hc.xls").Activate
to
Windows(thisworkbook.Worksheets("M").Range("A1").Value).Activate

That said, it is usually unecessary to activate workbooks and worksheets to
work with them

set bk = workbooks(thisworkbook.Worksheets("M").Range("A1").Value)
bk.Worksheets(1).Range("D7").Value = 21

as an example.

 

[shamble]

basically, what i'm going to do is insert the filename into cell a1 on
sheet m as hc060313.xls, the first thing my macro will do is save the
file to the name in cella1, followed by extracting data from another
sheet and pasting it into different sheets on hc060313.xls (or
whatever that document is saved as that week) - the code included is
just part of the pasting process.

so i guess my question is, can i get the macro to activate a sheet
based on what the name is in cella1?
and secondly while i'm here, how would i go about forcing the file to
save as the name in cell a1?

thanks for your help

 

[Tom Ogilvy]

My Best Guess at what you want is: (hard to know which book is being renamed
and which book contains M and which book contains ASC).

Dim bk as Workbook, sName as String
Chdir = "\\w2k6001\shared\csdgapp\miteam\Manpower"
Sheets("ASC").Select
sName = worksheets("M").Range("A1")
ActiveWorkbook.SaveAs sName
set bk = Workbooks.Open ( Filename:= _
"\\w2k6001\shared\CSDGAPP\miTeam\Manpower\AManpowerhcv0.2.xls")
bk.Worksheets("ASC").Range("D7:Q106").copy _
Destination:=Workbooks(sName).Worksheets(1).Range("D7")

 

[shamble]

Thanks again for your response Tom, sorry about this i'm not very good
at explaining what i'm trying to do. I think we're going down the right
path though. So, i've got a fixed workbook that is updated by someone
else. Im trying to get the data off that into my sheet. Their file is
the one located under \\w2k600\shared\csdgapp\miteam\Manpower and is
called AManpowerhcv0.2.xls. I'm trying to copy off the sheet in that
file called ASC to a file on my sheet called ASC also, but my sheet
will have a dynamic filename created depending on what is contained in
cell A1, sheet M of my file. Does this help at all?

I think the process should go something like this:
User opens my file from previous week, which is named according to the
date it was runt he previous week (yymmdd_hc.xls)
User enters file name into cell A1 of sheet M to be used for this weeks
update (060315_hc.xls for today for example)
User runs macro which saves file as the name in cell a1, sheet M of my
file
Macro copies data from AManpowerhcv0.2.xls to 060315_hc.xls although
this will have to be referenced to call A1 on sheet M due to the name
changing each week

Thanks again for your help

 

[Tom Ogilvy]

Assuming the workbook where the data will be copied to is the activeworkbook
when you run the macro in accordance with your description. (makes no
assumption about which workbook contains the macro).

Dim bk as Workbook, sName as String
Dim bk1 as Workbook
set bk1 = ActiveWorkbook
sName = worksheets("M").Range("A1")
if sname = "" then
msgbox "No name specified for the workbook"
application.Goto bk1.Worksheets("M").Range("A1"), True
exit sub
end if
set bk = Workbooks.Open( Filename:= _
"\\w2k6001\shared\CSDGAPP\miTeam\Manpower\AManpowerhcv0.2.xls")
bk.Worksheets("ASC").Range("D7:Q106").copy _
Destination:=bk1.Worksheets(ASC).Range("D7")
application.displayAlerts = False
bk1.SaveAs bk1.Path & "\" & sName
application.DispalyAlerts = True
bk.close Savechanges:=False