Const PATH = question

[xbxh]

I have a document that was created by somebody else. I am trying to break down how it works. I need to have this doc save to a mapped network drive but currently it is saving to a location on the local C: From digging through the macros in this report I found this section

Dim globChanged As Boolean
Const SAVE = 0
Const CHECK = 1
Const CHANGED = 2
Const PATH = "C:\Quotations as of 6-1-13\"
Dim globFName As String
Dim globFSuffix As String
Dim globFileList(1000) As String
Dim globFileCount As Integer

So I created a folder on the C: called Quotations as of 6-1-12 just to see if changing that would work. It did. All the macros fired off checked the folder to see if there was a similar file name then saved as a pdf and work book into that new folder. So my next test was changing the Const PATH= to Q:\ which is our mapped network drive. When I ran the macros it saved the document to C:\Users\(User name)\Documents. I am so confused. Any ideas?

 

[Jeffrey Mahoney]

Your network admin or windows may be redirecting the save from Q:\ to your documents. You should probably change the CONST PATH to read a value from a cell you select.

CONST PATH = Sheets("Sheet1").range("A1").value

Then you can name the path on the fly.

 

[xbxh]

Hmm when I do that I get a compile error Constant expression required and it highlights the .value part.

 

[Fluff]

You cannot set a const to a cell value.
When you changed the path did you use a valid file path?

 

[xbxh]

You cannot set a const to a cell value.
When you changed the path did you use a valid file path?

The path I am trying to use is a mapped network drive. So originally they were saving the documents to the C: drive but I need to change the path to the Q: drive which is a mapped network drive. I know the path is correct. It almost seems like the switching to Q drive is the issue but I am not sure why?

 

[Fluff]

You can quite happily saved to mapped drives, but if the path is not valid it will default to the active directory.
Is the Q drive a physical drive or is it mapped to a cloud site?

 

[xbxh]

Q drive is a physical drive on our server. So it has an IP address. I even tried using the IP address path instead of Q still no luck. Very strange.

 

[Fluff]

In that case can you please post your entire code.

 

[xbxh]

Option Explicit
Dim globChanged As Boolean
Const SAVE = 0
Const CHECK = 1
Const CHANGED = 2
Const PATH = "C:\Quotations as of 6-1-13\"
Dim globFName As String
Dim globFSuffix As String
Dim globFileList(1000) As String
Dim globFileCount As Integer

Sub SavePDF1()
Dim FName As String
Dim RetCode As Integer
Const SAVE = 0
Const CHECK = 1
Const CHANGED = 2
Dim FileExists As Boolean

   
    FName = Worksheets("Quotation").Range("QuoteCustomerName") & " " & Range("InputSunPartNumber") _
    & " " & Worksheets("Quotation").Range("QuoteNumber") ' & ".xls"
   
        FileExists = MatchFileName(FName)
        RetCode = vbNo
        If FileExists = True Then
            RetCode = MsgBox(FName & " already exists. Overwrite it ?", vbYesNo)
        End If
        If FileExists = False Or RetCode = vbYes Then
          
            Call SaveWorkBook(FName)
           
                 
            FName = Worksheets("Quotation").Range("QuoteCustomerName") & " " & Range("InputSunPartNumber") _
            & " " & Worksheets("Quotation").Range("QuoteNumber") & ".pdf"
            Call SaveInPDFFormat(FName)

        End If
   

End Sub
Function MatchFileName(ByVal FileNameToMatch As String) As Boolean
Dim fs, f, f1, fc, s
Dim RetCode As Boolean
Dim FolderSpec As String
   
    RetCode = False
    FileNameToMatch = FileNameToMatch & ".xls"
    FolderSpec = PATH
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(FolderSpec)
    Set fc = f.Files
    For Each f1 In fc
        s = f1.Name
        If FileNameToMatch = s Then
            RetCode = True
            Exit For
        End If
    Next
    MatchFileName = RetCode
   
End Function

Sub SaveWorkBook(ByVal FName As String)
'Dim RetCode As Integer
'Dim FName As String

   'Turn off the saveas warning message'
    Application.DisplayAlerts = False
    FName = FName & ".xls"
    ChDir PATH
    ActiveWorkbook.SaveAs FileName:=FName, _
        FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    'Turn saveas warning message back on
    Application.DisplayAlerts = True
   
End Sub

Sub SaveInPDFFormat(ByVal FName As String)
       
    ChDir PATH
    'Turn off the saveas warning message
    Application.DisplayAlerts = False
   
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=FName, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
   
    'Turn saveas warning message back on
    Application.DisplayAlerts = True
   
End Sub

 

[Fluff]

Thanks for that, the problem is that ChDir will only change the path in the active drive. You need to change the Drive to Q before changing the directory like

ChDrive "Q"
ChDir PATH