Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Open a new workbook with a specific name
Hi. Im seeking some help here.
Im trying to open via a Macro a new workbook, do some calcultations and then save this new workbook with a specific name. Problem is that this name is variable (It depends in this case of the Date brought from another workbook). How can i make my macro recogniza this date form the first workbook, and save the second one with this exact variable? This is what i've got so far: Sub exportmonth() Date = Range("b1").Value 'the date taken from the first workbook Cells.Select Selection.Copy Workbooks.Add ActiveWorkbook.SaveAs Filename:="H:\excel\test.xls", FileFormat:=xlNormal _ , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False End Sub In this case the new workbook is saved as text.xls but thats no use for me Any kinda help appreciated Thanx |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Open a new workbook with a specific name
Try this
Sub exportmonth() Const saveToPath = "H:\excel\" Dim myWorkbookName As String myWorkbookName = Format(Range("B1").Value, "[$-409]mmddyy;@") & ".xls" 'the date taken from the first workbook Cells.Select Selection.Copy Workbooks.Add ActiveWorkbook.SaveAs Filename:=saveToPath & myWorkbookName, FileFormat:=xlNormal _ , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False End Sub "Jepane" wrote: Hi. Im seeking some help here. Im trying to open via a Macro a new workbook, do some calcultations and then save this new workbook with a specific name. Problem is that this name is variable (It depends in this case of the Date brought from another workbook). How can i make my macro recogniza this date form the first workbook, and save the second one with this exact variable? This is what i've got so far: Sub exportmonth() Date = Range("b1").Value 'the date taken from the first workbook Cells.Select Selection.Copy Workbooks.Add ActiveWorkbook.SaveAs Filename:="H:\excel\test.xls", FileFormat:=xlNormal _ , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False End Sub In this case the new workbook is saved as text.xls but thats no use for me Any kinda help appreciated Thanx |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Open a new workbook with a specific name
It looks like you're just saving a new workbook--your code doesn't do any
pasting???? Option Explicit Sub exportmonth2() Dim myDate as Date dim ActSheet as worksheet Dim Wkbk as workbook set actsheet = activesheet myDate = actsheet.range("b1").value set wkbk = workbooks.add(1) 'single sheet 'copy to the new worksheet actsheet.cells.copy _ destination:=wkbk.worksheets(1).range("a1") wkbk.saveas filename:="H:\excel\" & format(mydate,"yyyy-mm-dd") & ".xls", _ FileFormat:=xlNormal 'close that new workbook? wkbk.close savechanges:=false End Sub You could copy the whole sheet to a new workbook, too--instead of copying the cells and pasting. Option Explicit Sub exportmonth2a() Dim myDate as Date dim ActSheet as worksheet set actsheet = activesheet myDate = actsheet.range("b1").value actsheet.copy 'creates a new workbook activeworkbook.saveas filename:="H:\excel\" _ & format(mydate,"yyyy-mm-dd") & ".xls", FileFormat:=xlNormal 'close that new workbook? activeworkbook.close savechanges:=false End Sub Jepane wrote: Hi. Im seeking some help here. Im trying to open via a Macro a new workbook, do some calcultations and then save this new workbook with a specific name. Problem is that this name is variable (It depends in this case of the Date brought from another workbook). How can i make my macro recogniza this date form the first workbook, and save the second one with this exact variable? This is what i've got so far: Sub exportmonth() Date = Range("b1").Value 'the date taken from the first workbook Cells.Select Selection.Copy Workbooks.Add ActiveWorkbook.SaveAs Filename:="H:\excel\test.xls", FileFormat:=xlNormal _ , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False End Sub In this case the new workbook is saved as text.xls but thats no use for me Any kinda help appreciated Thanx -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Open a new workbook with a specific name
Thats exaclty what i needed, to copy exactly the same file (and eliminating
some parts but that easy). Youv been of great help Dave Thanx "Dave Peterson" wrote: It looks like you're just saving a new workbook--your code doesn't do any pasting???? Option Explicit Sub exportmonth2() Dim myDate as Date dim ActSheet as worksheet Dim Wkbk as workbook set actsheet = activesheet myDate = actsheet.range("b1").value set wkbk = workbooks.add(1) 'single sheet 'copy to the new worksheet actsheet.cells.copy _ destination:=wkbk.worksheets(1).range("a1") wkbk.saveas filename:="H:\excel\" & format(mydate,"yyyy-mm-dd") & ".xls", _ FileFormat:=xlNormal 'close that new workbook? wkbk.close savechanges:=false End Sub You could copy the whole sheet to a new workbook, too--instead of copying the cells and pasting. Option Explicit Sub exportmonth2a() Dim myDate as Date dim ActSheet as worksheet set actsheet = activesheet myDate = actsheet.range("b1").value actsheet.copy 'creates a new workbook activeworkbook.saveas filename:="H:\excel\" _ & format(mydate,"yyyy-mm-dd") & ".xls", FileFormat:=xlNormal 'close that new workbook? activeworkbook.close savechanges:=false End Sub Jepane wrote: Hi. Im seeking some help here. Im trying to open via a Macro a new workbook, do some calcultations and then save this new workbook with a specific name. Problem is that this name is variable (It depends in this case of the Date brought from another workbook). How can i make my macro recogniza this date form the first workbook, and save the second one with this exact variable? This is what i've got so far: Sub exportmonth() Date = Range("b1").Value 'the date taken from the first workbook Cells.Select Selection.Copy Workbooks.Add ActiveWorkbook.SaveAs Filename:="H:\excel\test.xls", FileFormat:=xlNormal _ , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False End Sub In this case the new workbook is saved as text.xls but thats no use for me Any kinda help appreciated Thanx -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open Workbook to Specific Worksheet? | Excel Discussion (Misc queries) | |||
How to open a workbook on a specific worksheet. | Excel Worksheet Functions | |||
at startup, i would like a specific workbook to open. | Setting up and Configuration of Excel | |||
How do I open a workbook in a specific sheet | Excel Discussion (Misc queries) | |||
Open workbook to specific worksheet | Excel Discussion (Misc queries) |