2-Part Workbook Question- Save & Save As

[YellowLabPro]

I want to Save my workbook as is and create a new workbook that will save under a different name and the date.
I will post back part 2 after I work out part 1. There are some things I want to research on pt.2 before I post my initial question.

1) Fails: Error is- Compile. Method or data member not found.
Highlights Save


Sub SaveTgsIrc()
Dim WBTGS As Workbook
Dim WS1 As Worksheet
Set WS1 = Workbooks("TGSItemRecordCreatorMaster.xls").Worksheets("Record Creator")
WS1.Save
End Sub

[mdmackillop]

Hi Yelp
For the first part
ActiveWorkbook.SaveCopyAs "C:\AAA\Test.xls"

[mdmackillop]

I will post back part 2 after I work out part 1. There are some things I want to research on pt.2 before I post my initial question.
I think this looks like an endless loop with no exit. :rotlaugh:

[YellowLabPro]

Good Morning/Afternoon Malcolm
What was failing was the Set command. Why?
I had to add a second Set WbTgsIrc.
Why was Ws1 not good enough, it references the entire path, right?


Sub SaveTgsIrc()
Dim WbTgsIrc As Workbook
Dim Ws1 As Worksheet
Dim Ans As String
Set WbTgsIrc = Workbooks("TGSItemRecordCreatorMaster.xls")
Set Ws1 = Workbooks("TGSItemRecordCreatorMaster.xls").Worksheets("Record Creator")

Ans = MsgBox("Do You Want To Save This Workbook?", vbYesNo)
If a = vbNo Then Cancel = True
WbTgsIrc.Save
End Sub

[YellowLabPro]

I think this looks like an endless loop with no exit. :rotlaugh:

You know, I am starting to understand looping now-- so I at least know when I am in a "Joke" loop... :-)

[mdmackillop]

Option Explicit 'Always!!!

Sub SaveTgsIrc()
Dim WbTgsIrc As Workbook
Dim Ws1 As Worksheet 'Not used
Dim Ans As Long 'not String
Dim Cancel As Boolean 'not required

Set WbTgsIrc = Workbooks("TGSItemRecordCreatorMaster.xls")
Set Ws1 = Workbooks("TGSItemRecordCreatorMaster.xls").Worksheets("Record Creator") 'Why?

Ans = MsgBox("Do You Want To Save This Workbook?", vbYesNo) 'Ans here
If Ans = vbNo Then 'Changes to "a" here
Cancel = True 'Why?
Exit Sub 'You need to exit before the save command
Else
WbTgsIrc.Save
End If
End Sub

[mdmackillop]

Reduces to:
Sub SaveTgsIrc_1()
Dim WbTgsIrc As Workbook

Set WbTgsIrc = Workbooks("TGSItemRecordCreatorMaster.xls")
If MsgBox("Do You Want To Save This Workbook?", vbYesNo) _
= vbYes Then WbTgsIrc.Save
End Sub

[YellowLabPro]

Thanks M-

1) Why is Ans Long and not a string?
2) Why did you add Dim Cancel as Boolean and then say not required?
3) I actually nicked the code below:
Ans = MsgBox("Do You Want To Save This Workbook?", vbYesNo)
If a = vbNo Then Cancel = True
from the help file, I am not sure exactly how this is working. Their original one was:
a = MsgBox("Do you really want to save the workbook?", vbYesNo)
If a = vbNo Then Cancel = True
I think I need to change the "a" to Ans.

New Code:

Sub SaveTgsIrc()
Dim WbTgsIrc As Workbook
Dim Ans As Long
Set WbTgsIrc = Workbooks("TGSItemRecordCreatorMaster.xls")

Ans = MsgBox("Do You Want To Save This Workbook?", vbYesNo)
If Ans = vbNo Then Cancel = True
Else
WbTgsIrc.Save
End If
End Sub

[mdmackillop]

BTW rather than
Set WbTgsIrc = Workbooks("TGSItemRecordCreatorMaster.xls")
Set Ws1 = Workbooks("TGSItemRecordCreatorMaster.xls").Worksheets("Record Creator")
Since you have a variable for the workbook, use it in the second line
Set WbTgsIrc = Workbooks("TGSItemRecordCreatorMaster.xls")
Set Ws1 = WbTgsIrc.Worksheets("Record Creator")

[YellowLabPro]

Ok--- Newer code, compliments Malcom...


Sub SaveTgsIrc()
Dim WbTgsIrc As Workbook

Set WbTgsIrc = Workbooks("TGSItemRecordCreatorMaster.xls")

If MsgBox("Do You Want To Save This Workbook?", vbYesNo) _
= vbYes Then WbTgsIrc.Save
End Sub

[YellowLabPro]

BTW rather than
Set WbTgsIrc = Workbooks("TGSItemRecordCreatorMaster.xls")
Set Ws1 = Workbooks("TGSItemRecordCreatorMaster.xls").Worksheets("Record Creator")
Since you have a variable for the workbook, use it in the second line
Set WbTgsIrc = Workbooks("TGSItemRecordCreatorMaster.xls")
Set Ws1 = WbTgsIrc.Worksheets("Record Creator")


Good One, thanks.

How about clue me in on the Ans as Long instead of Ans as String variable?

[mdmackillop]

1. From Help



MsgBox Function


Displays a message in a dialog box, waits for the user to click a button, and returns an Integer indicating which button the user clicked.
You could use Integer, but they get changed to Long in any case.
vbYes = 6, vbNo = 7

2. In repeating your code I had to Dim Cancel to avoid an error. I meant that your code made no use of this value.

3. There is a workbook event BeforeSave which has Cancel as a parameter. Setting Cancel = True aborts the save

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Ans As Long
Ans = MsgBox("Do You Want To Save This Workbook?", vbYesNo)
If Ans = vbNo Then Cancel = True
End Sub

[YellowLabPro]

Thanks Malcolm.
I am off to work on the SaveCopyAs now.

I am sure I will have ????? regarding this guy.

Thanks buddy, you are awesome!

[YellowLabPro]

Malcolm,
I just caught something. Why did we not need an End If in the procedure?

[mdmackillop]

If the code is on a single line then End if is not required. The LineBreak effectively keeps the code on one line.

ie
If x = 2 then y = 3

'or
If x = 2 _
then Y = 3

'or
If x = 2 then
y = 3
End If

[YellowLabPro]

My Path is erroring:
Ideas??


Sub SaveTgsIrcCopies()
Dim WbTgsIrc As Workbook
Set WbTgsIrc = Workbooks("TGSItemRecordCreatorMaster.xls")
Application.DisplayAlerts = False
WbTgsIrc.SaveCopyAs("C:\TGSFiles\TGSItemRecordCreator" & Format(Date, "mmddyy") & ".xls"
Application.DisplayAlerts = True


End Sub

[YellowLabPro]

If the code is on a single line then End if is not required. The LineBreak effectively keeps the code on one line.

ie
If x = 2 then y = 3

'or
If x = 2 _
then Y = 3

'or
If x = 2 then
y = 3
End If



Aahhhhhhh-
You're kind of tricky aren't you :rofl:

[mdmackillop]

Match the brackets!
WbTgsIrc.SaveCopyAs("C:\TGSFiles\TGSItemRecordCreator" & Format(Date, "mmddyy") & ".xls"

[lucas]

WbTgsIrc.SaveCopyAs "C:\TGSFiles\TGSItemRecordCreator" & Format(Date, "mmddyy") & ".xls"

[YellowLabPro]

Got it Malcolm.
Hi Lucas,
Great, thanks.... I am still absorbing syntax rules. Your alternate method helps clarify things for me.

Thanks guys,

YLP

[mdmackillop]

When you're putting long strings together, it's sensible to assign it to a variable where you can check for missing elements, eg "\" in folder\name strings. Once it's all working, you can remove them if desired

Sub Macro1()
Dim tmp As String
tmp = "C:\TGSFiles\TGSItemRecordCreator" & Format(Date, "mmddyy") & ".xls"
Debug.Print tmp
WbTgsIrc.SaveCopyAs tmp
End Sub

[Bob Phillips]

Aahhhhhhh-
You're kind of tricky aren't you :rofl:

Maybe, but that is a bad programming practice IMO.

I would go further, I think even single action If stattements should be broken over two lines, thereby forcing the third, End If, line. This way, no time is EVER wasted trying to match If ... End If where no match is required.

[mdmackillop]

I wouldn't argue the point. You will come across it, so best to be aware of it.
I'll probably still use single line Ifs in such as
If a = vbNo Then Cancel = True

[YellowLabPro]

Part 2:
Trying to save as a .CSV file. Erroring on The .SaveCopyAs


Sub SaveTgsIrcImport()
Dim WbTGSU As Workbook
Set WbTGSU = Workbooks("TGSUpdater.xls")
Application.DisplayAlerts = False
WbTGSU.Save
WbTGSU.SaveCopyAs "C:\Importin- " & Format(Date, "mm-dd-yy") & ".txt", FileFormat:=xlCSV
Application.DisplayAlerts = True
End Sub

[mdmackillop]

You can only save a single sheet as a csv file, so make a copy of the sheet to a new book, save that book and close it. This leaves the original file open.
BTW you have .txt in your code.

Sub Macro3()
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:="C:\Importin- " & Format(Date, "mm-dd-yy") & ".csv", _
FileFormat:=xlCSV
Application.DisplayAlerts = False
ActiveWindow.Close
Application.DisplayAlerts = True
End Sub

[YellowLabPro]

Got it... I will alter my code to reflect that method.

I have the format for the date, can I add the timestamp following the date? I have not found anything on this yet.

[mdmackillop]

Just use Now instead of Date; adjust the format to suit.
Format(Now, "mm-dd-yy hh mm ss")

[Bob Phillips]

I wouldn't argue the point. You will come across it, so best to be aware of it.
No need to, as I said ... IMO. I just wouldn't employ you if you coded like that :devil2:(big devil grin - couldn't add a smile, what is wrong with my account?).
I'll probably still use single line Ifs in such as
If a = vbNo Then Cancel = True As I said, I wouldn't. One time of searching for a non-existant End If will cost many times what it costs to break it up.

Edit: Happy to oblige :devil2:

[YellowLabPro]

Hi Bob,
Sorry I did not reply to your post. It seemed to have skipped over all of yours. I just saw it and went back up and found an earlier one too. Thanks, I see your point.

Ok... Here is where I am. I have everything working up to where you left off Malcolm. I want to create two more copies of the new sheet, both w/ different names,
1) Importin- and the time stamp .csv
2) Import.dat
How I went about it was With End With. This might be totally off the charts of what is allowed, but if it is allowed I thought it a clever way to handle this.
I will need to close them afterwards, which I have left alone for the time being.


Sub SaveTgsIrcImport()
Dim WbTGSU As Workbook
Dim WsU As Worksheet
Set WbTGSU = Workbooks("TGSUpdater.xls")
Set WsU = Workbooks("TGSUpdater.xls").Worksheets("Update")
Application.DisplayAlerts = False
WsU.Activate
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:="C:\TGSFiles\Importin- " & Format(Now, "mm-dd-yy hh mm ss") & ".csv", _
FileFormat:=xlCSV
WsU.Rows(1).Delete
With ActiveWorkbook
.ActiveSheet.SaveCopyAs Filename:="C:\TGSFiles\Importin- " & Format(Date, "mm-dd-yy") & ".dat", _
FileFormat:=xlCSV
.ActiveSheet.SaveCopyAs Filename:="C:\TGSFiles\Import.dat", _
FileFormat:=xlCSV
End With
'ActiveWindow.Close

Application.DisplayAlerts = True
End Sub

[mdmackillop]

Hi Yelp,
Comments on your code.
Option Explicit 'Always!!!

Sub SaveTgsIrcImport()
Dim WbTGSU As Workbook
Dim WsU As Worksheet
Set WbTGSU = Workbooks("TGSUpdater.xls")
'Use the variable name
Set WsU = Workbooks("TGSUpdater.xls").Worksheets("Update")
Application.DisplayAlerts = False
'Combine next two lines into one
WsU.Activate
ActiveSheet.Copy
'This saves the copied sheet as a csv file
ActiveWorkbook.SaveAs Filename:="C:\TGSFiles\Importin- " & Format(Now, "mm-dd-yy hh mm ss") & ".csv", _
FileFormat:=xlCSV
'This deletes a row from your original workbook/worksheet. Is this correct?
WsU.Rows(1).Delete
'The activeworkbook below is the CSV file
With ActiveWorkbook
'SaveCopyAs is a Workbook function and not valid here
.ActiveSheet.SaveCopyAs Filename:="C:\TGSFiles\Importin- " & Format(Date, "mm-dd-yy") & ".dat", _
FileFormat:=xlCSV
'SaveCopyAs is a Workbook function and not valid here
.ActiveSheet.SaveCopyAs Filename:="C:\TGSFiles\Import.dat", _
FileFormat:=xlCSV
End With
'ActiveWindow.Close

Application.DisplayAlerts = True
End Sub

[mdmackillop]

Suggested method
Sub SaveTgsIrcImport2()
Dim WbTGSU As Workbook
Dim WsU As Worksheet
Set WbTGSU = Workbooks("TGSUpdater.xls")
Set WsU = WbTGSU.Worksheets("Update")
Application.DisplayAlerts = False
WsU.Copy
With ActiveWorkbook
.SaveAs Filename:="C:\TGSFiles\Importin- " & Format(Now, "mm-dd-yy hh mm ss") & ".csv", _
FileFormat:=xlCSV
.SaveAs Filename:="C:\TGSFiles\Importin- " & Format(Date, "mm-dd-yy") & ".dat", _
FileFormat:=xlCSV
.SaveAs Filename:="C:\TGSFiles\Import.dat", _
FileFormat:=xlCSV
End With
ActiveWindow.Close

Application.DisplayAlerts = True
End Sub

[YellowLabPro]

M-
I am having a look now.
Option Explicti is always used, it is in the top of my project and does not get copied to the board.

[YellowLabPro]

Is this what you mean as for using the Variable?


Set WbTGSU = Workbooks("TGSUpdater.xls")
Set WsU = Workbooks(WbTGSU).Worksheets("Update")

[mdmackillop]

See my suggested code,

Your code as posted would be used where you have the File name as a string, not set as a workbook
eg
Dim WsU As Worksheet
Dim WbName As String
WbName = "Test.xls"
Set WsU = Workbooks(WbName).Worksheets("Update")

[YellowLabPro]

Sub SaveTgsIrcImport2()
Dim WbTGSU As Workbook
Dim WsU As Worksheet
Workbooks.Open Filename:="C:\TGSFiles\TGSUpdater.xls" 'Need Code If Open ignore
Set WbTGSU = Workbooks("TGSUpdater.xls")
'Set WsU = Workbooks("TGSUpdater.xls").Worksheets("Update")
'Tried using Variable, but this failed, I tried it w/ and w/o quotes around variable name
Set WsU = Workbooks("WbTGSU").Worksheets("Update")
Application.DisplayAlerts = False
WbTGSU.Save
WsU.Activate '.Copy ' Tried Wsu.Activate.Copy but this failed
ActiveSheet.Copy
With ActiveWorkbook
.SaveAs Filename:="C:\TGSFiles\Importin- " & Format(Now, "mm-dd-yy hh mm ss") & ".csv", _
FileFormat:=xlCSV
WsU.Rows(1).Delete ' Yes this is correct
.SaveAs Filename:="C:\TGSFiles\Importin- " & Format(Now, "mm-dd-yy hh mm ss") & ".dat", _
FileFormat:=xlCSV
.SaveAs Filename:="C:\TGSFiles\Import.dat", _
FileFormat:=xlCSV
End With
'ActiveWindow.Close
Application.DisplayAlerts = True
End Sub

[YellowLabPro]

Sorry Malcolm,
I still don't see how you want me to use the variable for the Workbook. I looked through your suggested method, but it looks the same as mine....

[Bob Phillips]

Is this what you mean as for using the Variable?


Set WbTGSU = Workbooks("TGSUpdater.xls")
Set WsU = Workbooks(WbTGSU).Worksheets("Update")
No, WbTGSU is a workbook object, so just use that in the next statement, not as an element of the Workbooks collection
Set WbTGSU = Workbooks("TGSUpdater.xls")
Set WsU = WbTGSU.Worksheets("Update")

[mdmackillop]

WsU.Activate '.Copy ' Tried Wsu.Activate.Copy but this failed
ActiveSheet.Copy
Simply
Wsu.Copy

[YellowLabPro]

Ok... on both points.

The reason I want to keep the code this way is I want to activate the worksheet.


WsU.Activate
ActiveSheet.Copy


During this process I have other sheets that I am working on and do not want to get caught w/ the wrong sheet on top.
Maybe a little over cautious on my part....

[YellowLabPro]

Ok Malcolm,
We got it. There is one thing left if you could help me understand?
The worksheets all closed except for TGSUpdater.xls, it closed the two newly created sheets Import*.* w/ the line- ActiveWindow.Close.

My question is why or how did it close two sheets, they were both not active at the same time, and this was not a loop?

In answering this, I hope to grasp some insight how to close TGSUpdater.xls in the same process.
I realize we can add another line to handle this separately if need be.



Sub SaveTgsIrcImport()
Dim WbTGSU As Workbook
Dim WsU As Worksheet
On Error Resume Next
Workbooks("TGSUpdater.xls").Activate
If Err <> 0 Then Workbooks.Open ("C:\TGSFiles\TGSUpdater.xls")
On Error GoTo 0
Set WbTGSU = Workbooks("TGSUpdater.xls")
Set WsU = WbTGSU.Worksheets("Update")
Application.DisplayAlerts = False
WbTGSU.Save
WsU.Activate
ActiveSheet.Copy
With ActiveWorkbook
.SaveAs Filename:="C:\TGSFiles\Importin- " & Format(Now, "h-mm-ss am/pm m-dd-yy") & ".csv", _
FileFormat:=xlCSV
WsU.Rows(1).Delete
.SaveAs Filename:="C:\TGSFiles\Importin- " & Format(Now, "h-mm-ss am/pm m-dd-yy") & ".dat", _
FileFormat:=xlCSV
.SaveAs Filename:="C:\TGSFiles\Import.dat", _
FileFormat:=xlCSV
End With

ActiveWindow.Close
Application.DisplayAlerts = True
End Sub

[mdmackillop]

With is not the same as a loop. It's a shorthand method of using the same object description on multiple lines.
Each SaveAs closes the previous window so at the end there is only one to close. To close the original workbook, this would be
ActiveWorkbook.Close Save:=True 'or False

'or
WbGTSU.Close Save:=True 'or False

[YellowLabPro]

Malcolm,
Thanks for all your assistance today!

YLP,
or as you like to make me YELP.... good times

[Bob Phillips]

Closing a window doesn't do anything with regard to worksheets and workbooks. It just closes the window that the worksheet is displayed within, but the workbook is still open.

[YellowLabPro]

Bob,
If that is the case then how do I handle closing the two newly created worksheets. The thing is, these books are no longer open. Is the code handling this in a way I do not see?

Thanks for your help.




Sub SaveTgsIrcImport()
Dim WbTGSU As Workbook
Dim WsU As Worksheet
On Error Resume Next
Workbooks("TGSUpdater.xls").Activate
If Err <> 0 Then Workbooks.Open ("C:\TGSFiles\TGSUpdater.xls")
On Error GoTo 0
Set WbTGSU = Workbooks("TGSUpdater.xls")
Set WsU = WbTGSU.Worksheets("Update")
Application.DisplayAlerts = False
WbTGSU.Save
WsU.Activate
ActiveSheet.Copy
With ActiveWorkbook
.SaveAs Filename:="C:\TGSFiles\Importin- " & Format(Now, "h-mm-ss am/pm m-dd-yy") & ".csv", _
FileFormat:=xlCSV
WsU.Rows(1).Delete
.SaveAs Filename:="C:\TGSFiles\Importin- " & Format(Now, "h-mm-ss am/pm m-dd-yy") & ".dat", _
FileFormat:=xlCSV
.SaveAs Filename:="C:\TGSFiles\Import.dat", _
FileFormat:=xlCSV
End With

ActiveWindow.Close
Application.DisplayAlerts = True
End Sub