PDA

View Full Version : Solved: Todays Date and Time



Klartigue
04-10-2012, 12:52 PM
Is there a way to do the below code for the current time, not the current date?

todaysdate = Application.InputBox("Enter Today's Date for File Name", "Today's Date")


And I would like the have the current time saved in the file name just as the date is below:

ChDir "G:\Charles Luke\Back Office\Trade Blotter"

ActiveWorkbook.Saveas Filename:="Trades" + " " + "(" + todaysdate + ")" + ".xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

Klartigue
04-10-2012, 12:54 PM
I tried the below code but it doesn't save the time as part of the file name??

MyDate = Application.InputBox("Enter Today's Time (HH:MM:SSSS)", "Today's Time")

ChDir "G:\Katherine Lartigue\Allocations\"
ActiveWorkbook.Saveas Filename:="G:\Katherine Lartigue\Allocations\Fidelity Trades" + " " + "(" + todaysdate + ")" + "(" + todaystime + ")" + ".xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Bob Phillips
04-10-2012, 02:02 PM
Why ask for the date and time, get it from the system



ChDir "G:\Katherine Lartigue\Allocations\"
ActiveWorkbook.SaveAs Filename:="G:\Katherine Lartigue\Allocations\Fidelity Trades" & " " & _
format(now,"(yyyy-mm-dd) (hh.mm.ss)") + ".xls", _
FileFormat:=xlExcel8, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

Klartigue
04-10-2012, 02:06 PM
there is an error. It highlights the word format in this part..

format(now,"(yyyy-mm-dd) (hh.mm.ss)") + ".xls", _

and says ambiguous name detected.

Bob Phillips
04-10-2012, 04:12 PM
Not sure. I would change the + to & but I don't think that is the problem.

Can you post the workbook?

frank_m
04-11-2012, 12:07 AM
HI Bob,

I see in Klartigue's code the file type .xlsx , so I believe she needs to use .xlsm

Also I think there is a forward slash missing in the File Name Path. (Fidelity Trades\)

And the File Version for a 2007 .xlsm is 50 (edit: I changed 51 to 50)
Instead of FileFormat:=xlExcel8, should be FileFormat:=50

Would you agree ?

ChDir "G:\Katherine Lartigue\Allocations\"

'added a forward slash in the path below after Fidelity Trades\
ActiveWorkbook.SaveAs Filename:="G:\Katherine Lartigue\Allocations\Fidelity Trades\" & " " & _
Format(Now, "(yyyy-mm-dd) (hh.mm.ss)") + ".xlsm", _
FileFormat:=50, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
Edit: Corrected .xls - changed to .xlsm

Edit# 2 -- Klartigue - The "ambiguous name detected" error is usually from having two macros with the same name

Bob Phillips
04-11-2012, 12:09 AM
I saw Fidelity Trades as part of the filename Frank, not part of the path. I think you are right about the xlsm, but that did not cause an error for me, just got a warning.

frank_m
04-11-2012, 12:31 AM
I saw Fidelity Trades as part of the filename Frank, not part of the path. .
Awe yeah, you're ofcourse correct

I don't have Excel 2007 here but, but do have the conversion tool installed in 2003 and it is erroring because of the command xlExcel8
-- Isn't 8 version 2000 ?

Edit: I looked it up and see that version 8 is Excel 97, but it does seem that it should work because of backward compatability.
However on my machine it errors something like "Save as workbook object class failed"
When I changed xlExcel8 to xlNormal, it works. :think:

Bob Phillips
04-11-2012, 01:12 AM
I am using Excel 2010 Frank, and as I say, that seems to work fine.

shrivallabha
04-11-2012, 06:35 AM
Bob's code works for me including the plus sign. I use Excel 2007.

Not sure but see if this works which is just a variation.
ActiveWorkbook.SaveAs Filename:="G:\Katherine Lartigue\Allocations\Fidelity Trades" + " " + "(" + Format(Date, "dd-mm-yyyy") + ")" + "(" + Format(Now, "hh.mm.ss") + ")" + ".xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Aflatoon
04-11-2012, 06:52 AM
An ambiguous name error would imply a sub or function called Format I think.

Klartigue
04-11-2012, 09:21 AM
Hmm I still cant get it to work. Is there a way to work with the below:


Public Sub Saveas()
Dim MyTime
Dim MyDate
MyDate = Date
MyTime = Time


With ActiveSheet

ChDir "G:\Katherine Lartigue\Allocations\"
ActiveWorkbook.Saveas Filename:="G:\Katherine Lartigue\Allocations\Fidelity Trades" & Date & Time & ".xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False



End With

End Sub

Bob Phillips
04-11-2012, 09:26 AM
Post the whole workbook Katherine, and let us dig deeper.

Klartigue
04-11-2012, 09:30 AM
I have posted the workbook. I am using Excel 2010. I have saved the workbook as Excel 97-2003 workbook.

Bob Phillips
04-11-2012, 09:31 AM
Where is all of your code?

Klartigue
04-11-2012, 09:34 AM
Sub Trades()
' Save blotter
With ActiveSheet
ChDir "C:\Users\klartigue\Desktop"
ActiveWorkbook.Saveas Filename:="C:\Users\klartigue\Desktop\Trades.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End With

End Sub
Public Sub ProcessData()

'Delete block allocations

Dim Lastrow As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = Lastrow To 1 Step -1

If Cells(i, "A").Value2 = "Block" Then

.Rows(i).Delete
End If
Next i
End With

Application.ScreenUpdating = True

End Sub
Sub Sheet1()
Sheets.Add After:=Sheets(Sheets.Count)
Application.Run "BLPLinkReset"
Sheets("Blotter").Select
Application.Run "BLPLinkReset"
End Sub
Sub FIDO()
' Copy and paste fidelity allocations into new sheet in workbook

Dim Lastrow As Long
Dim i As Long
With ActiveSheet

Lastrow = .Cells(.Rows.Count, "W").End(xlUp).Row
For i = 4 To Lastrow

If .Cells(i, "W").Value = "FIDELITY" Then

With Range(.Cells(i, "A"), .Cells(i, "W")).Select
Selection.Cut
Sheets("Sheet1").Select
Range("A10000").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Blotter").Select
End With

End If

Next i

End With

End Sub
Sub SortByBroker()
'Sort trades by broker
Range("A4:AA124").Select
ActiveWorkbook.Worksheets("Blotter").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Blotter").Sort.SortFields.Add Key:=Range("W4:W44") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Blotter").Sort
.SetRange Range("A3:W44")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Sub DeleterowA()
Sheets("Sheet1").Select

Rows("1:1").Select
Selection.Delete Shift:=xlUp
End Sub

Sub Macro10()
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
With ActiveWindow
End With
Range("G1").Select
ActiveCell.FormulaR1C1 = "Trade Date"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Settle Date"
Columns("I:I").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 2.29
Columns("J:J").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 17.86
Range("J1").Select
ActiveCell.FormulaR1C1 = "Master Account"
Columns("K:K").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("K:K").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 2.57
Columns("L:L").ColumnWidth = 3.14
Range("M1").Select
ActiveCell.FormulaR1C1 = "Cusip/Symbol"

End Sub
Sub AthroughH()
Columns("A:C").Select
Range("C1").Activate
Selection.Delete Shift:=xlToLeft
Columns("D:E").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Columns("C:C").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 1.29
Columns("H:H").Select
Selection.Cut
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("F:F").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("M:M").Select
Selection.Cut
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Range("H1").Select
ActiveCell.FormulaR1C1 = "B/S"
Range("H2").Select

End Sub
Sub More()
Columns("E:E").ColumnWidth = 2.29
Columns("F:F").ColumnWidth = 2.14
Columns("I:AA").Select
Selection.Cut
Range("AC1").Select
ActiveSheet.Paste
Columns("I:I").ColumnWidth = 0.67
Columns("J:J").ColumnWidth = 0.67
Columns("K:K").ColumnWidth = 0.58
Columns("L:L").ColumnWidth = 0.58
Columns("M:M").ColumnWidth = 0.42
Columns("N:N").ColumnWidth = 0.42
Columns("O:O").ColumnWidth = 0.67
Columns("P:P").ColumnWidth = 0.58
Columns("Q:Q").ColumnWidth = 0.58
Columns("R:R").ColumnWidth = 0.67
Columns("S:S").ColumnWidth = 0.5
Columns("T:T").ColumnWidth = 0.5
Columns("U:U").ColumnWidth = 0.58
Columns("V:V").ColumnWidth = 0.33
Columns("W:W").ColumnWidth = 0.42
Columns("X:X").ColumnWidth = 0.42
Columns("Y:Y").ColumnWidth = 0.33
Columns("Z:Z").ColumnWidth = 0.33
Columns("AA:AA").ColumnWidth = 0.5
Columns("AB:AB").ColumnWidth = 0.5
Columns("AC:AC").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 7.57
Selection.ColumnWidth = 11
Range("AC1").Select
ActiveCell.FormulaR1C1 = "Total Block Quantity"
Range("AC2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[13]:R[9]C[13])"
Range("AC2").Select
ActiveCell.FormulaR1C1 = "=SUM(R2C42:R11C42)"
Range("AC2").Select
Selection.AutoFill Destination:=Range("AC2:AC11")
Range("AC2:AC11").Select
Range("AC3").Select
ActiveWindow.SmallScroll Down:=-24
Range("AC2").Select
Selection.AutoFill Destination:=Range("AC2:AC11")
Range("AC2:AC11").Select

End Sub
Sub more2()
Columns("AL:AL").Select
Selection.Cut
Columns("AD:AD").Select
Selection.Insert Shift:=xlToRight
Range("AD1").Select
ActiveCell.FormulaR1C1 = "Price"
Range("AE1").Select
ActiveCell.FormulaR1C1 = "Executing Broker MPID"
End Sub
Sub Final()
Rows("1:1").RowHeight = 33
Range("AE1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("AE:AE").ColumnWidth = 12.43
Columns("AE:AE").ColumnWidth = 13.71
Columns("AF:AF").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("AF1").Select
ActiveCell.FormulaR1C1 = "Executing Broker DTC #"
Columns("AG:AG").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("AG1").Select
ActiveCell.FormulaR1C1 = "Time"
Columns("AV:AV").Select
Selection.Cut
Columns("AH:AH").Select
Selection.Insert Shift:=xlToRight
Range("AH1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.FormulaR1C1 = "Interest"
Columns("AI:AI").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("AT:AT").Select
Selection.Cut
Columns("AJ:AJ").Select
Selection.Insert Shift:=xlToRight
Range("AJ1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.FormulaR1C1 = "Quantity"
Columns("AI:AI").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("AJ:AJ").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("AI:AI").ColumnWidth = 0.83
Columns("AU:AU").Select
Selection.Cut
Columns("AJ:AJ").Select
ActiveSheet.Paste
Range("AJ1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.FormulaR1C1 = "Client Acct"
Range("AG16").Select

End Sub
Sub Tweeking()
' Tweeking Format
Columns("AM:BA").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("D3").Select
ActiveWindow.SmallScroll Down:=-3
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D11"), Type:=xlFillDefault
Range("D2:D11").Select
Selection.NumberFormat = "0.00"
Selection.NumberFormat = "0.0"
Selection.NumberFormat = "0"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("AC:AC").ColumnWidth = 8
Columns("AE:AE").ColumnWidth = 12
Range("AE1:AF1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("AG1:AL1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("XFD1:AL1").Select
Range("AL1").Activate
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("AK:AK").ColumnWidth = 0.67
Columns("A:A").ColumnWidth = 11.43
Columns("A:A").ColumnWidth = 11
Columns("AG:AG").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("AG:AG").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 0.5
Columns("AH:AH").ColumnWidth = 0.67

End Sub
Sub FidoMasterAcct()
' insert fidelity master account number
Dim NumBlocks As Long
Dim Lastrow As Long
Dim i As Long

With ActiveSheet
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To Lastrow
.Cells(i, "A").Offset(0, 3).Select
ActiveCell.Value = "911754798"
Next i
End With

End Sub
Sub OpenBrokerCode()
Workbooks.Open Filename:= _
"G:\Katherine Lartigue\Instructions\Broker Codes.xlsx"

End Sub
Sub ActivateSheet()
Windows("Trades.xls").Activate
End Sub
Sub LookupDTC()
' Look up broker DTC numbers
Dim NumBlocks As Long
Dim Lastrow As Long
Dim i As Long

With ActiveSheet
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To Lastrow


.Cells(i, "A").Offset(0, 31).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[Broker Codes.xlsx]Sheet1'!R1:R1048576,3,FALSE)"

Next i
End With
End Sub
Sub OpenAccountnumbers()
' Open Alob sheet
Workbooks.Open Filename:= _
"G:\Katherine Lartigue\Allocations\Account numbers.xls"
End Sub
Sub Accounts()
' Lookup entire fidelity account numbers
Windows("Trades.xls").Activate
Dim NumBlocks As Long
Dim Lastrow As Long
Dim i As Long

With ActiveSheet
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To Lastrow
.Cells(i, "A").Offset(0, 41).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-4],'[Account numbers.xls]Book1'!R1:R65536,4,FALSE)"
Next i
End With

End Sub
Sub OpenFidoSpreadsheet()
Workbooks.Open Filename:= _
"G:\Katherine Lartigue\Allocations\Fidelity spreadsheet master.xls"
End Sub
Sub PasteValues()
' Copy and paste values in spreadsheet
Windows("Trades.xls").Activate
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D16").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("Q17").Select

End Sub
Sub CPaccounts()
'Paste account numbers as values
Range("AP2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Range("AL2").Select
ActiveSheet.Paste

End Sub
Sub Copy()
' Copy and Paste data into Fidelity master spreadsheet

Range("A2:AN252").Select
Selection.Copy
Application.CutCopyMode = False
Selection.Copy
Application.CutCopyMode = False
Selection.Copy
Windows("Fidelity spreadsheet master.xls").Activate
Application.Run "BLPLinkReset"
Range("A9").Select
ActiveSheet.Paste

End Sub

Public Sub Saveas()
Dim MyTime
Dim MyDate
MyDate = Date
MyTime = Time


With ActiveSheet

ChDir "G:\Katherine Lartigue\Allocations\"
ActiveWorkbook.Saveas Filename:="G:\Katherine Lartigue\Allocations\Fidelity Trades" & Date & Time & ".xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False



End With

End Sub

Aflatoon
04-11-2012, 09:41 AM
Try using:
MyDate = vba.format(Date, "yyyy-mm-dd")

Klartigue
04-11-2012, 09:43 AM
Public Sub Saveas()
Dim MyTime
Dim MyDate
MyDate = VBA.Format(Date, "yyyy-mm-dd")
MyTime = Time


With ActiveSheet

ChDir "G:\Katherine Lartigue\Allocations\"
ActiveWorkbook.Saveas Filename:="G:\Katherine Lartigue\Allocations\Fidelity Trades" & Date & Time & ".xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False



End With

End Sub

I tried that and the error is "Method 'Save As' of object '_Workbook' failed"

Bob Phillips
04-11-2012, 09:56 AM
This is working fine for me in your workbook with all of your code


Public Sub Saveas()
Dim MyDateTime
MyDateTime = Format(Now, "(yyyy-mm-dd) (hh.mm.ss)")

With ActiveSheet

ChDir "C:\Users\Bob\Desktop\"
ActiveWorkbook.Saveas Filename:="C:\Users\Bob\Desktop\Fidelity Trades" & MyDateTime & ".xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End With
End Sub

Klartigue
04-11-2012, 10:00 AM
For some reason I am getting the ambiguous name detected compile error again and it is highlighting the word "format" in this part of the code

MyDateTime = Format(Now, "(yyyy-mm-dd) (hh.mm.ss)")

Bob Phillips
04-11-2012, 10:15 AM
Post your workbook again, exactly as it is, complete with compile error.

Klartigue
04-11-2012, 02:19 PM
For some reason I am getting the ambiguous name detected compile error again and it is highlighting the word "format" in this part of the code


MyDateTime = Format(Now, "(yyyy-mm-dd) (hh.mm.ss)")

Aussiebear
04-12-2012, 02:21 AM
Works here using Katherine's file and Bob's code

Aflatoon
04-12-2012, 02:57 AM
Again, try:
MyDateTime = VBA.Format(Now, "(yyyy-mm-dd) (hh.mm.ss)")

If that works, you have another routine called Format somewhere.

Bob Phillips
04-12-2012, 03:03 AM
She has already said that she gets some other error with that, and her workbook works fine for me and Ted without that code. She clearly has some problem, but it is not manifesting itself when we try it with her file.

@Katherine, can you try it on another machine?

Aflatoon
04-12-2012, 03:08 AM
No, she was getting a SaveAs error but that was because the code used Date and Time rather than the variables declared:
Dim MyTime
Dim MyDate
MyDate = VBA.Format(Date, "yyyy-mm-dd")
MyTime = Time


With ActiveSheet

ChDir "G:\Katherine Lartigue\Allocations\"
ActiveWorkbook.Saveas Filename:="G:\Katherine Lartigue\Allocations\Fidelity Trades" & Date & Time & ".xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

which should have been:
Dim MyTime
Dim MyDate
MyDate = VBA.Format(Date, "yyyy-mm-dd")
MyTime = VBA.Format(Time, "hh.mm.ss")


With ActiveSheet

ChDir "G:\Katherine Lartigue\Allocations\"
ActiveWorkbook.Saveas Filename:="G:\Katherine Lartigue\Allocations\Fidelity Trades" & MyDate & MyTime & ".xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

for example.

Whenever VBA.Format is not used, she has an ambiguous name error with Format, hence I believe there is another routine called Format.

shrivallabha
04-12-2012, 08:12 AM
ChDir in the code is redundant as filepath is explicitly set. I have tried using worksheetfunction which should bypass the word "Format" completely.

Dim Fn As WorksheetFunction
Dim sDtTime As String
Set Fn = Application.WorksheetFunction
sDtTime = Fn.Text(Now, "(yyyy-mm-dd hh.mm.ss)")
ActiveWorkbook.SaveAs Filename:="G:\Katherine Lartigue\Allocations\Fidelity Trades " & sDtTime & ".xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False