Variable not being consistant in excel vba macro

Question

I have a macro in excel that if a drive exists the macro saves the file to my harddrive and thumbdrive. If it doesn't exist, it saves to the harddrive. When the macro runs I am getting an error. Here is the macro:

Sub SaveFile()

Dim fso As Scripting.FileSystemObject
Set fso = New Scripting.FileSystemObject

Dim filepath As String

name = "Siemens"
filepath = "F:\Dave backup\Open Orders\Label Manifests\Active  Labels Manifest\Manifest Related\File saving testing folder\" & name & "\" & name & " Manifest " & Format(Now, "mm-dd-yyyy")

 If fso.DriveExists("F:\") = True Then

        'ActiveWorkbook.SaveAs filename:="C:\Users\dgray\Documents\" & name & " Manifest " & Format(Now, "mm-dd-yyyy")
        'ActiveWorkbook.SaveAs filename:="F:\Dave backup\Open Orders\" & name & "\" & name & " Manifest " & Format(Now, "mm-dd-yyyy")
        ActiveWorkbook.SaveAs filename:=filepath
    Else

         'ActiveWorkbook.SaveAs filename:="C:\Users\dgray\Documents\" & name & " Manifest " & Format(Now, "mm-dd-yyyy")
        ActiveWorkbook.SaveAs filename:="F:\Dave backup\Open Orders\Label Manifests\Active  Labels Manifest\Manifest Related\File saving testing folder\" & name & "\" & name & " Manifest " & Format(Now, "mm-dd-yyyy")


    End If

End Sub

Here is the error I am getting:

I don't know if you can see but the last part of the error message says "\Siemens\8E555720. That should also say the customer name (i.e. Siemens). In the code I have set the customer name in the variable "name". So why is it giving me this crazy error? All help is appreciated.

I can't see your linked image cause domain is restricted on my end, so I don't know the error. But if what you pasted is your exact code as-is, then your Else statement is trying to save-as to drive F which it just verified is not there. Maybe you meant for the else to only save to drive C:? — ionizing
– ionizing, Commented May 4, 2020 at 20:03

Tim Williams · Accepted Answer · 2020-05-04 21:39:42Z

1

Something like this might be better:

Sub SaveFile()

    Const PATH_C As String = "C:\Users\dgray\Documents\"
    Const PATH_F As String = "F:\Dave backup\Open Orders\Label Manifests\" & _
        "Active Labels Manifest\Manifest Related\File saving testing folder\"

    Dim fileName As String, custName As String

    custName = "Siemens"

    fileName = custName & " Manifest " & Format(Now, "mm-dd-yyyy") & ".xlsx" 'or .xlsm

    ActiveWorkbook.SaveAs fileName:=PATH_C & fileName 'assume C is always available

    'save to F if available
    If Len(Dir(PATH_F)) > 0 Then
        'assumes the custName folder already exists...
        ActiveWorkbook.SaveAs fileName:=PATH_F & custName & "\" & fileName
    End If

End Sub

answered May 4, 2020 at 21:39

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user11871683 Over a year ago

I will try this tomorrow and let you know how it works. Where is it checking to see if F drive exists?

Tim Williams Over a year ago

If Len(Dir(PATH_F)) > 0 checks to see if the path defined in the constant PATH_F is a valid path: it check the whole path, not just F:\

user11871683 Over a year ago

Ok. That part doesn't work. The folders I have listed in the code block are for testing purposes. I will verify the filepath and let you know the results.

Tim Williams Over a year ago

"That part doesn't work" - that doesn't tell me much. What does it do instead of work?

Bhargav Rao · Accepted Answer · 2020-05-05 00:07:45Z

0

I can see the space in folder name which may cause this error.

By removing space in the foldername this error would be fixed.

edited May 5, 2020 at 0:07

Bhargav Rao

52.6k29 gold badges130 silver badges142 bronze badges

answered May 4, 2020 at 20:08

Sahil Kandroo

1591 gold badge4 silver badges15 bronze badges

4 Comments

user11871683 Over a year ago

When I am using this, there is an F drive (my thumbdrive as backup). It works for other customers I use this for, but for some reason this is the only one I am having issues with.

Sahil Kandroo Over a year ago

By removing space in the foldername this error still show up?

user11871683 Over a year ago

I can't remove the spaces. Those are the folders on my network.

Sahil Kandroo Over a year ago

vbforums.com/…. This will help you chr$(34)

Collectives™ on Stack Overflow

Variable not being consistant in excel vba macro

2 Answers 2

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related