Save new workbook with specific file name

[annep]

Workbook Checkreg.xls runs a macro, which formats the data to an ascii text format. The finished product now is in column I. I copied and pasted column I into a new workbook. I got that so far. My problem comes with naming the new file.

The new file should be called "CompanyName" & cell F2 (which is a date) from the checkreg.xls.
I am not sure how to switch back and forth between the workbooks to get the date into the new book.

[Simon Lloyd]

You could save it like this

Please Login or Register  to view this content.

this is assuming that your "F2" is on sheet1.

Regards,
Simon

[Mallycat]

=I am not sure how to switch back and forth between the workbooks to get the date into the new book.

You don't need to switch back to get the date. You just need to reference the cell directly in your code.

Please Login or Register  to view this content.

Note the use of Format(F2, "ddmmyyyy") in setting the book name. This is to convert the Excel date serial number to a normal formatted date.

Matt

[peejay]

I use the following code attached to a button to take the contents of cell C6, add “timestamp” to it and check if it exists. If it doesnt exist the file is saved, if the file does exist then an “a” is added to the contents of cell C6, a “timestamp” is added and then the file is saved.

Sub File_control()
'
' Macro recorded to change file name and fill cell with result
' Also adds time stamp to file name then checks if file exists and re-name it by adding
' "a" to file name if it does
'
'
Set fs = Application.FileSearch
Dim Originalname As String
Dim Newname As String
Dim Extension As String
Dim FileExists
Dim Mymonth As String
Dim Myday As String
Dim Myyear As String
Dim Myhour As String
Dim Myminute As String
Dim Stampfile As String
'
'
Dim Startname As String
Dim Endname As String
‘
'Collect timestamp information for use later
Mymonth = Month(Now)
If Len(Mymonth) = 1 Then Mymonth = "0" & Mymonth
Myday = Day(Now)
If Len(Myday) = 1 Then Myday = "0" & Myday
Myyear = Right(Year(Now), 2)
Myhour = Hour(Now)
If Len(Myhour) = 1 Then Myhour = "0" & Myhour
Myminute = Minute(Now)
If Len(Myminute) = 1 Then Myminute = "0" & Myminute
Stampfile = Myday & "-" & Mymonth & "-" & Myyear & " " & Myhour & "-" & Myminute

' Check if file exists, keep original name if it doesnt and change name if it does
'
With fs
'
' define full path to storage folder (change this to suit your path)
.LookIn = "c:\documents and settings\peter\my documents\saved data files"
'
' get Startname for file from Cell C6
'
.Filename = Range("c6").Value
' at this point you can use .filename = “Company Name” & Range(“c6”).value
Extension = ".xls"
'
Originalname = .LookIn & "\" & .Filename & " " & Stampfile & Extension
'
MsgBox Originalname
'
FileExists = (Dir(Originalname) <> "")
'
MsgBox FileExists
'
If FileExists = "True" Then
Newname = .LookIn & "\" & .Filename & "a " & Stampfile & Extension
Endname = .Filename & "a " & Stampfile & Extension
MsgBox Originalname & " - ALREADY EXISTS ------------------------ THIS FILE WILL BE RENAMED and saved as " & Newname
Else
Newname = Originalname
Endname = .Filename & " " & Stampfile & Extension
MsgBox Originalname & " - DOES NOT EXIST ------------------------ THIS FILE will be saved as " & Newname
End If
End With

'
' put final name for saved file into c7
Range("c7").Select
ActiveCell.Value = Endname

'Store file using whatever name has been decided in function above (note use name that includes FULL path)
'
'
ActiveWorkbook.SaveAs Filename:=Newname
'
'

'THE END.............................
'
End Sub

[annep]

Thank you for so much info. I will try all of them out.
Thanks,
Anne

[annep]

I used Simon Lloyds version which was the easiest to understand for me.
However, just realized, I need to put a path in there somewhere. Something like below, but it does not work:

Sub Macro1()
Dim D
D = Sheets("Sheet1").Range("F2").Value
Columns("I:I").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
With ActiveWorkbook
.SaveAs Filename:="F:\BankUploads\CoName" & " " & Format$(D, "dd mm yyyy") & ".csv", FileFormat:=xlCSV
End With
ActiveWorkbook.Close
End Sub

[peejay]

Try building the filename as a string variable

At top of code
Dim Savename as string

' define full path to storage folder (change this to suit your path)
.LookIn = " F:\BankUploads\CoName"


At bottom of code
Savename = .LookIn & “\” & " " & Format$(D, "dd mm yyyy") & ".csv", FileFormat:=xlCSV
With ActiveWorkbook
.SaveAs Filename:= Finalname
ActiveWorkbook.Close

[annep]

This line comes up and red and highligted is the comma after the ".csv"

Savename = .LookIn & “\” & " " & Format$(D, "dd-mm-yy") & ".csv", FileFormat:=xlCSV

[annep]

I put the apostrophe in front of that problem line and tried to debug the beginning. Excel also comes up with an error message for the below line for the path

.LookIn = " F:\pp\BankUploads"

highlighted n red: .Lookin =

Error message: compile error, Invalid or unqualified reference.

[Simon Lloyd]

Hi Ann try this

Please Login or Register  to view this content.

Regards,
Simon

[annep]

Works like a charm.
In case anyone else is interested, the CoName is actually part of the filename.
I changed the line to:

WorkPath = "F:\BankUploads\" & "CoName" & " " & Format$(D, "dd mm yyyy")

Thank you,
Anne

[annep]

I have to come back one more time. Need your help again Simon.

I am using the same macro after transferring it to two different computers, I get an error message I do not get on mine at this line

With ActiveWorkbook
.SaveAs Filename:=WorkPath & ".csv", FileFormat:=xlCSV
End With

Run-time error 1004
Methos "SaveAs" of object '_Workbook' failed.

One computer is running Office 2003 and the other XP. What am I missing on those machines?

Thanks,
Anne

[Simon Lloyd]

Anne, i'm no expert!.....but,

The problem probably lies with the hard coded WorkPath though

Please Login or Register  to view this content.

does this exist on the other machines?

regards,
Simon