Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
File referencing in macro
Hi,
I'm still very new to this, so please bear with me: I am developing a series of macros that create a user-defined workbook from a standard template, and I would like to know how to refer to a workbook in a macro when it has been saved under a name and file path specified by the user. The final action of the macros in the template is to save the workbook under the name sepcified by the user in cell "B3" of the 'setup sheet'. If the user does not specify a file path (in "B6"), the workbook default-saves to the C:\Temp folder. This obviously means that the workbook could have any filepath and any name. (The template will (hopefully) be issued to various dislocated cost centres for their use). When I try to run a macro in the new workbook, the macros still refer to the standard template and therefore do not run. The macro (DATA_EXTRACT, that I cannot run) in the new workbook extracts data from an external data file and pastes it into the workbook, where it is then used to update various other sheets of the workbook. This is what I currently have: Sub SAVE_SHEETS If Sheets("SETUP SHEET").Range("B6") = "" Then ActiveWorkbook.SaveAs FileName:="C:\TEMP\" & Sheets("SETUP SHEET").Range ("B3") & " MASTER SHEETS.xls", FileFormat _:=xlNormal Else ActiveWorkbook.SaveAs FileName:=Sheets("SETUP SHEET").Range("B6") & Sheets("SETUP SHEET").Range("B3") & " MASTER SHEETS.xls", FileFormat _:=xlNormal, End If End Sub Sub DATA_EXTRACT Application.ScreenUpdating = False Workbooks.Open FileName:="C:\ Temp \ DATA_FILE.xls" Range("C2:D6000 , H2:H6000").Copy Windows(**FILE NAME??**).Activate Sheets("DATA_SHEET").Select ActiveSheet.Paste Windows("DATA_FILE").Activate ActiveWindow.Close 'etc etc... I hope this makes sense and someone understands what I'm trying to do. (Maybe there's a way to create macros that aren't specific to a named workbook???) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing closed file | Excel Worksheet Functions | |||
Referencing another file/workbook | Excel Worksheet Functions | |||
referencing a file name and sheet name | Excel Discussion (Misc queries) | |||
Referencing a 2nd file | Excel Discussion (Misc queries) | |||
Referencing a closed file | Excel Worksheet Functions |