open new workbook

[John]

How do I open and name a new workbook with only 1 worksheet in vb? I
tried learning how using record macro but couldn't get things to work.

thanks

John

 

[JP]

It would help if you elaborated on "couldn't get things to work" --
can you post the code that was generated by the recorder?

Thx,
JP

 

[Mark Ivey]

See if the following code will do what you want...

Mark Ivey


Sub create_new_workbook()
Dim newWB As Workbook

Workbooks.Add

Application.DisplayAlerts = False

Sheets("Sheet2").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Sheet3").Select
ActiveWindow.SelectedSheets.Delete

Application.DisplayAlerts = True

If FileExists("C:\MyNewWorkbook.xls") Then
MsgBox "This file already exists!"
Else
ActiveWorkbook.SaveAs Filename:="C:\MyNewWorkbook.xls"
End If

End Sub

' Function from...
' http://www.exceltip.com/st/Determine_if_a_workbook_
' exists_using_VBA_in_Microsoft_Excel/471.html
Function FileExists(FullFileName As String) As Boolean
' returns TRUE if the file exists
FileExists = Len(Dir(FullFileName)) > 0
End Function

 

[Gary Keramidas]

how about something like this:

Sub test2()
Dim wb1 As Workbook
Dim shts As Long
Dim fpath As String

fpath = ActiveWorkbook.Path & "\"
With Application
shts = .SheetsInNewWorkbook
.SheetsInNewWorkbook = 1
Workbooks.Add
.SheetsInNewWorkbook = shts
End With
ActiveWorkbook.SaveAs Filename:=fpath & "test.xls"
End Sub

 

[John]

that;'s kind of what I do...

Workbooks.Add

ActiveWorkbook.SaveAs Filename:="C:\Users\John\Documents\Make_new.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Application.CommandBars("Stop Recording").Visible = False
End Sub


When the workbook is first opened it is called Book1. This creates
problems because SOMETIMES there is already another book1 workbook open
or book2 or both or more. Hard to explain other than bad programing.

I'd like to be able to rename the new workbook before I save it. Is that
possible? I tried something like

Workbooks.Add Filename: ="New_File.xls" but compiler didn't go for that.


OR

Just save one of the worksheets in the current workbook as a new
workbook with a a name I assign it. That's what I'm really trying to do.

John

 

[Mark Ivey]

Here is the situation...

If you only have one instance of Excel open and use the WORKBOOKS.ADD
command, it will automatically increment the number at the end of the file.
It is only in temporary memory at that point. If you want to give it a
specific name, you will have to use the SAVEAS command.

Now if you have 2 instances of Excel open you might run into problems... but
I am not totally sure without more information.

Have you tested the code I sent yet? What reaction are you getting from it?
Is it working at all?

You can always change the past in the code I sent to the path and filename
you prefer.

Let me know how I can assist further.

Mark Ivey

 

[Gary Keramidas]

give this a try, just change the sheet names and workbook names

Sub test3()
Dim wbk2 As Workbook
Dim wbk1 As Workbook
Dim shts As Long

Set wbk1 = ActiveWorkbook
With Application
shts = .SheetsInNewWorkbook
.SheetsInNewWorkbook = 1
Set wbk2 = Workbooks.Add
wbk2.Worksheets(1).Name = "New Name"
wbk1.Worksheets("Sheet1").Cells.Copy _
wbk2.Worksheets("New Name").Cells
wbk2.SaveAs Filename:="new workbook.xls"
.SheetsInNewWorkbook = shts
End With
End Sub