CreateObject from Template

[Paul3rd]

Hello, I can't get the following code to execute correctly. It creates a new
worksheet, but not from a template as I intended.
Can anyone help?

Sub CTMacro()
On Error Resume Next
Dim ExcelSheet As Object

Application.DisplayAlerts = False
Set ExcelSheet = CreateObject("Excel.Sheet")
Set ExcelSheet = ExcelSheet.Workbooks.Add("C:\CTest.xlt")

With Workbooks("Copy of ApptDis.xls")
.Worksheets("Sheet1").Range("B2:I2").Copy
ExcelSheet.Application.Visible = True
With ExcelSheet.ActiveSheet.Range("B2:I2")
.PasteSpecial xlPasteValues

ExcelSheet.SaveAs "C:\CTest.xls"
Application.DisplayAlerts = True
End With
End With
End Sub

Thanks in advance for any help.
Paul

 

[Dave Peterson]

Dim ExcelSheet As Object
set ExcelSheet = sheets.Add(type:="c:\ctest.xlt")

 

[Paul3rd]

Dave, Thanks for your reply, I changed the code to the following but no new
workbook/worksheet is created.

 

[Dave Peterson]

Remove or comment the "on error resume next" line.

I bet that it's masking an error.

(You sure that C:\cTest.xlt actually exists?)

 

[Paul3rd]

Hello Dave,
You are correct, I didn't write the complete path to CTest.xlt.
I have the code working correctly now (in a new module) except for one small
thing...
The Jan5copytest.xls closes after code executes and I want to keep it open.
(I tried inserting a line Workbook.Open("C:\Jan5copytest') but it didn't work.

 

[Dave Peterson]

I don't understand.

I thought you wanted a worksheet added to an existing workbook--not a new
workbook.

And if Jan5copytest.xls is already open (you used it in your With statement),
why are you trying to open it again.

And is there a reason you're not copying the code from the VBE and pasting it
into your message.

To me, those ">" brackets mean that the stuff you posted isn't the same as the
code you're testing.

Hello Dave,
You are correct, I didn't write the complete path to CTest.xlt.
I have the code working correctly now (in a new module) except for one small
thing...
The Jan5copytest.xls closes after code executes and I want to keep it open.
(I tried inserting a line Workbook.Open("C:\Jan5copytest') but it didn't work.

 

[Paul3rd]

Dave, sorry for the confusion.
My original code did create a new workbook.
Now, Jan5copytest.xls is open, it has a command button that triggers the
module.
The module creates a new workbook from the template (CTest.xlt), copies a
range to it, saves the new workbook as (CCTest.xls) and then closes it.
Which is what I wanted.
The module is also closing Jan5copytest.xls which is not what I wanted.
I won't use the > character anymore.
Paul

 

[Dave Peterson]

How about posting your current version of the code?

Dave, sorry for the confusion.
My original code did create a new workbook.
Now, Jan5copytest.xls is open, it has a command button that triggers the
module.
The module creates a new workbook from the template (CTest.xlt), copies a
range to it, saves the new workbook as (CCTest.xls) and then closes it.
Which is what I wanted.
The module is also closing Jan5copytest.xls which is not what I wanted.
I won't use the > character anymore.
Paul

 

[Dave Peterson]

ps. What's the name of the workbook that holds this code?

 

[Paul3rd]

The name of the workbook that holds this code is Jan5copytest.xls.
This is the current code.
Sub NewModule()
Dim MyWB As Workbook

Application.DisplayAlerts = False
Set MyWB = Workbooks.Add("C:\FolderAppt\CTest.xlt")
With Workbooks("Jan5copytest.xls")
.Worksheets("Sheet1").Range("B2:I2").Copy

MyWB.Application.Visible = False
With MyWB.Sheets("Sheet1").Range("B2:I2")
.PasteSpecial xlPasteValues

MyWB.SaveAs ("C:\CCTest.xls")
MyWB.Close (False)

Application.DisplayAlerts = True
End With
End With

End Sub

ps. What's the name of the workbook that holds this code?

 

[Dave Peterson]

I didn't test this, but I don't think your program is closing Jan5CopyTest.xls.

But you are hiding the excel application with this line:
MyWB.Application.Visible = False

I'm not sure why you'd want to do this, but if there is a reason, you'll
probably(!) want to make it visible before the code ends:

Application.Visible = True

If you add this line after myWb is closed, then the reference to myWb won't be
good.

In fact, I'm not sure why you didn't just use:
Application.Visible = False
on that other line.

ps. Since the workbook that owns the code is Jan5CopyTest.xls, it would
probably be safer to use:

With thisworkbook
instead of:
With Workbooks("Jan5copytest.xls")

Then you won't have to worry about what happens if/when that file is renamed.

You have a few with/end with structures that really don't have much between
those lines.

I think that this is easier to read (after you delete the comments!):

Option Explicit
Sub NewModule()
Dim MyWB As Workbook

Set MyWB = Workbooks.Add("C:\FolderAppt\CTest.xlt")

ThisWorkbook.Worksheets("Sheet1").Range("B2:I2").Copy

'I'd just use the topleft cell of the range.
'Excel will adjust the pasted range to match the
'dimensions of the copied range
MyWB.Worksheets("Sheet1").Range("B2").PasteSpecial
Paste:=xlPasteValues

'no need for ()' in the next two lines
Application.DisplayAlerts = False
MyWB.SaveAs Filename:="C:\CCTest.xls"
Application.DisplayAlerts = True

MyWB.Close savechanges:=False

End Sub

The name of the workbook that holds this code is Jan5copytest.xls.
This is the current code.
Sub NewModule()
Dim MyWB As Workbook

Application.DisplayAlerts = False
Set MyWB = Workbooks.Add("C:\FolderAppt\CTest.xlt")
With Workbooks("Jan5copytest.xls")
.Worksheets("Sheet1").Range("B2:I2").Copy

MyWB.Application.Visible = False
With MyWB.Sheets("Sheet1").Range("B2:I2")
.PasteSpecial xlPasteValues

MyWB.SaveAs ("C:\CCTest.xls")
MyWB.Close (False)

Application.DisplayAlerts = True
End With
End With

End Sub

 

[Dave Peterson]

ps.

If you haven't turned your pc off since your last test, try this:

Close all the versions of excel that you have open (that you can see)
Hit alt-ctrl-delete to show the windows Task manager.
Look at the Applications tab. You shouldn't see excel anywhere.

Look at the Processes tab.
You may find that you have lots and lots of excel.exe's still running (but
they're not visible) to you (the user).

You can end these processes, but you won't get a chance to save any work.

When I'm hiding excel and testing, I want a method of showing those instances of
excel.

I use a .vbs file with this in it:

dim myXL
On Error Resume Next
Set myXL = GetObject(, "Excel.Application")
If Err.Number = 429 Then
msgbox "Excel is not running"
else
myxl.visible = true
end If
On Error GoTo 0
Set myxl = nothing



You can copy this (starting with Dim and ending with Set) and paste it into
NotePad.

Then save the file as UnHideExcel.VBS in a nice safe location on your pc.

Then you can close any visible instances of excel
run that .vbs file
close that newly shown instance of excel

run that .vbs file
close the next newly shown instance of excel

(do those last two steps until you get the "excel is not running" message.)


<<snipped>>

 

[Paul3rd]

Dave, Good idea!
The following code works just fine, things might have been confused by the
MyWB designation. I changed that to NewWKB, Closed it and added
Application.Visible = True.
Thanks for all your help. I'm just learning to write code and things can get
hairy!
Paul
Sub NewModule()
Dim NewWKB As Workbook

Application.DisplayAlerts = False
Set NewWKB = Workbooks.Add("C:\FolderAppt\CTest.xlt")
NewWKB.Application.Visible = False
With Workbooks("Copy of ApptDis.xls")
.Worksheets("Sheet1").Range("B2:I2").Copy

With NewWKB.Sheets("Sheet1").Range("B2:I2")
.PasteSpecial xlPasteValues

NewWKB.SaveAs ("C:\CCTest.xls")
NewWKB.Close (True)
Application.Visible = True

End With
End With

End Sub

I didn't test this, but I don't think your program is closing Jan5CopyTest.xls.

But you are hiding the excel application with this line:
MyWB.Application.Visible = False

I'm not sure why you'd want to do this, but if there is a reason, you'll
probably(!) want to make it visible before the code ends:

Application.Visible = True

If you add this line after myWb is closed, then the reference to myWb won't be
good.

In fact, I'm not sure why you didn't just use:
Application.Visible = False
on that other line.

ps. Since the workbook that owns the code is Jan5CopyTest.xls, it would
probably be safer to use:

With thisworkbook
instead of:
With Workbooks("Jan5copytest.xls")

Then you won't have to worry about what happens if/when that file is renamed.

You have a few with/end with structures that really don't have much between
those lines.

I think that this is easier to read (after you delete the comments!):

Option Explicit
Sub NewModule()
Dim MyWB As Workbook

Set MyWB = Workbooks.Add("C:\FolderAppt\CTest.xlt")

ThisWorkbook.Worksheets("Sheet1").Range("B2:I2").Copy

'I'd just use the topleft cell of the range.
'Excel will adjust the pasted range to match the
'dimensions of the copied range
MyWB.Worksheets("Sheet1").Range("B2").PasteSpecial
Paste:=xlPasteValues

'no need for ()' in the next two lines
Application.DisplayAlerts = False
MyWB.SaveAs Filename:="C:\CCTest.xls"
Application.DisplayAlerts = True

MyWB.Close savechanges:=False

End Sub

 

[Dave Peterson]

Changing the variable name from myWB to newWKB doesn't make the code any more
clear.

You may want to review that code that I suggested to see if is equivalent and
more clear. (I think that it is.)

Dave, Good idea!
The following code works just fine, things might have been confused by the
MyWB designation. I changed that to NewWKB, Closed it and added
Application.Visible = True.
Thanks for all your help. I'm just learning to write code and things can get
hairy!
Paul
Sub NewModule()
Dim NewWKB As Workbook

Application.DisplayAlerts = False
Set NewWKB = Workbooks.Add("C:\FolderAppt\CTest.xlt")
NewWKB.Application.Visible = False
With Workbooks("Copy of ApptDis.xls")
.Worksheets("Sheet1").Range("B2:I2").Copy

With NewWKB.Sheets("Sheet1").Range("B2:I2")
.PasteSpecial xlPasteValues

NewWKB.SaveAs ("C:\CCTest.xls")
NewWKB.Close (True)
Application.Visible = True

End With
End With

End Sub