Const PATH = question

xbxh

New Member
Joined
Jan 13, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
2,020
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 13, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hmm when I do that I get a compile error Constant expression required and it highlights the .value part.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,339
Office Version
  1. 365
Platform
  1. Windows
You cannot set a const to a cell value.
When you changed the path did you use a valid file path?
 

xbxh

New Member
Joined
Jan 13, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,339
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 13, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,339
Office Version
  1. 365
Platform
  1. Windows
In that case can you please post your entire code.
 

xbxh

New Member
Joined
Jan 13, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
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
 
Last edited by a moderator:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,339
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
ChDrive "Q"
ChDir PATH
 
Solution
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,579
Messages
5,770,958
Members
425,653
Latest member
UNSING

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top