Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference filename used in code from cell
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\AManpowe rhcv0.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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference filename used in code from cell
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").V alue).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. -- Regards, Tom Ogilvy " wrote: 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\AManpowe rhcv0.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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference filename used in code from cell
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 :) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference filename used in code from cell
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\AManpowe rhcv0.2.xls") bk.Worksheets("ASC").Range("D7:Q106").copy _ Destination:=Workbooks(sName).Worksheets(1).Range( "D7") -- Regards, Tom Ogilvy " wrote: 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 :) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference filename used in code from cell
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference filename used in code from cell
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\AManpowe rhcv0.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 -- Regards, Tom Ogilvy " wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell reference to a filename | Excel Discussion (Misc queries) | |||
Excel - print to .pdf using filename reference from cell | Excel Discussion (Misc queries) | |||
substitute the filename in a cell reference with a string in another cell. | Excel Discussion (Misc queries) | |||
Cell Reference is Worksheet Name in VB Code | Excel Programming | |||
Reference in a filename.. | Excel Worksheet Functions |