Creating a file in VBA Macro and saving it in a specific location

[anshikam]

Hello,
Trying to create a file and save it in a specific location with the below code.

Error code I get is :

Run Time Error '424'
Object Required


Report = "RMC_Indemnity_Report" & a & " " & b & c & d & ".xls"
Dim Path As String
Path = "F:\Corporate\Anshika\Due Dates\sent\"

Workbooks.Add
ActiveWorkbooks.SaveAs Path & "\" & Report

 

[mohadin]

Hi
Try

Sub test()
    Report = "RMC_Indemnity_Report" & "a" & " " & "b" & "c" & "d" & ".xls"
    Dim Path As String
    Path = "F:\Corporate\Anshika\Due Dates\sent"
    Workbooks.Add
    ActiveWorkbook.SaveAs Path & "\" & Report
End Sub

So "\" is doubled one of them should be deleted "path"f:.....\sent"
then ActiveWorkbook.save as not ActiveWorkbooks

 

[anshikam]

Hi
Try

Sub test()
    Report = "RMC_Indemnity_Report" & "a" & " " & "b" & "c" & "d" & ".xls"
    Dim Path As String
    Path = "F:\Corporate\Anshika\Due Dates\sent"
    Workbooks.Add
    ActiveWorkbook.SaveAs Path & "\" & Report
End Sub

So "\" is doubled one of them should be deleted "path"f:.....\sent"
then ActiveWorkbook.save as not ActiveWorkbooks

Sorry my bad.
However fixing this still gives me an error:

Run Time Error '424'
Object Required

Report = "RMC_Indemnity_Report" & a & " " & b & c & d & ".xls"
Dim Path As String
Path = "F:\Corporate\Anshika\Due Dates\sent\"
Workbooks.Add
ActiveWorkbooks.SaveAs Path & Report

 

[mohadin]

You still having

ActiveWorkbooks.SaveAs Path & Report

ActiveWorkbook.SaveAs Path & Report

And What about a, b, c, and d

 

[anshikam]

You still having

ActiveWorkbooks.SaveAs Path & Report

ActiveWorkbook.SaveAs Path & Report

And What about a, b, c, and d

This is oly part of a larger code...a b c d is the Date Time Stamp since i do not want to overwrite the earlier file.
Updated code has been fixed please see below. Same issue....


Dim Report As String
Dim a As Date
Dim b As Integer
Dim c As Integer
Dim d As Integer

a = Date
b = Hour(Now())
c = Minute(Now())
d = Second(Now())

'MsgBox a & b & c & d
Report = "RMC_Indemnity_Report" & a & " " & b & c & d & ".xls"
'MsgBox report
Dim Path As String
Path = "F:\Corporate\Anshika\Due Dates\sent\"

Workbooks.Add
ActiveWorkbooks.SaveAs Path & Report

 

[mohadin]

Ok that's fine for a b c d
Then ActiveWorkbook without "s" at the end

 

[anshikam]

I h

This is only part of a larger code...a b c d is the Date Time Stamp since i do not want to overwrite the earlier file.
Updated code has been fixed please see below. Same issue....


Dim Report As String
Dim a As Date
Dim b As Integer
Dim c As Integer
Dim d As Integer

a = Date
b = Hour(Now())
c = Minute(Now())
d = Second(Now())

'MsgBox a & b & c & d
Report = "RMC_Indemnity_Report" & a & " " & b & c & d & ".xls"
'MsgBox report
Dim Path As String
Path = "F:\Corporate\Anshika\Due Dates\sent\"

Workbooks.Add
ActiveWorkbooks.SaveAs Path & Report

I had my system changed and these codes have been working fine for months.....let me look at this again however earlier i was Adding and saving the file in 1 statement and it worked just fine.

Workbooks.Add.SaveAs Filename:=Report

 

[mohadin]

Right
If the the date something like "05/11/2020"
will not work as a part of file name
What you could do

Dim a as String
.
.
.
a=Date
a=Replace(a, "/", "_")

 

[anshikam]

Right
If the the date something like "05/11/2020"
will not work as a part of file name
What you could do

Dim a as String
.
.
.
a=Date
a=Replace(a, "/", "_")

Right
If the the date something like "05/11/2020"
will not work as a part of file name
What you could do

Dim a as String
.
.
.
a=Date
a=Replace(a, "/", "_")

Have fixed the filename.....that was working just fine even before. The error I get is at the last line ActiveWorkbooks.SaveAs Path & Report.

Dim Report As String
Dim CDateTime As String
CDateTime = Day(Now()) & Month(Now()) & Year(Now()) & "_" & Hour(Now()) & Minute(Now()) & Second(Now())
Report = "RMC_Indemnity_Report" & CDateTime & ".xls"
Dim Path As String
Path = "F:\Corporate\Anshika\Due Dates\sent\"

Workbooks.Add
ActiveWorkbooks.SaveAs Path & Report

 

[anshikam]

Hi
Try

Sub test()
    Report = "RMC_Indemnity_Report" & "a" & " " & "b" & "c" & "d" & ".xls"
    Dim Path As String
    Path = "F:\Corporate\Anshika\Due Dates\sent"
    Workbooks.Add
    ActiveWorkbook.SaveAs Path & "\" & Report
End Sub

So "\" is doubled one of them should be deleted "path"f:.....\sent"
then ActiveWorkbook.save as not ActiveWorkbooks

ok Finally this worked with Workbooks.Add.SaveAs Filename:=Path & Report

Thanks for all your help.