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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.