Trying to link a variable to a public const

**ttd** · 09-26-2012, 11:27 AM

I have a list of file directories located on a second module using public const (I think this is a way to house the data?). On Module #1 I have a macro that has a prompt to enter text into an inputbox. Inside the inputbox I will be entering a name ie Andy. I would like to make a new variable that looks up Andy on the public const & defind that variable as the file directory for Andy. I am basically trying to automate saving an excel doc as a PDF to a specific folder base on the name I type into the inputbox. Can someone please help? Below are the macros I have now

Module #1

Sub PDF()

Dim MyInput As String
Dim Client As String
Dim Location As String
Dim Quarter As String

This_Year = Year(Now())
Report_Date = Right(Range("A4").Value, 10)

If Format(Report_Date, "mm/dd") = "03/31" Then
Report_Name = " 1Q " & This_Year & " Account Overview"
ElseIf Format(Report_Date, "mm/dd") = "06/30" Then
Report_Name = " 2Q " & This_Year & " Account Overview"
ElseIf Format(Report_Date, "mm/dd") = "09/30" Then
Report_Name = " 3Q " & This_Year & " Account Overview"
ElseIf Format(Report_Date, "mm/dd") = "12/31" Then
Report_Name = " 4Q " & This_Year & " Account Overview"
Else
Report_Name = "OD AO as of " & Report_Date
End If

MyInput = InputBox("Enter Client Last Name", _
"MyInputTitle", "Enter your input text HERE")

Client = MyInput

Location = 'need to be able to link what I type into the InputBox & a list of Public Consts on another module'

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, Filename:=Location & Client & Report_Name & ".pdf"

ActiveWorkbook.Close

End Sub

Module #2

Public Const Tim= "G:\Users\Tim\"

Public Const Bob = "G:\Users\Bob\"

Public Const Andy = "G:\Users\Andy\"

**rvasquez** · 09-26-2012, 11:42 AM

Hello there,

Can you please add code tags around you post? Also, what values will be entered into the text box? For instance what is entered into the textbox to make the variable location equal to Public Const Tim= "G:\Users\Tim\" and so on?

Thanks!

**ttd** · 09-26-2012, 11:54 AM

I will be entering a client name into the inputbox & using that name in the save as string (saving the pdf as "client name 1/2/3/4 Q Account Overview". So basically, the client name I type into the inputbox has two functions. 1. used in save as string and 2. used to determine which file location that client should be saved to based on the public const I set up for each client. Each client has a different file location (separate folder) & I am trying to be able to type just a client name & my macro will save it in the correct folder. Thanks

**MrShorty** · 09-26-2012, 12:05 PM

As structured, I'm thinking a Select Case statement might be the easiest to program:

Select Case myinput
case "tim"
client=tim
case "bob"
client=bob
...
end select

**ttd** · 09-26-2012, 12:21 PM

Thank you. I have over 100 client folders so will that many case statements slow down the macro? I guess there really isn't a better way to store these directories in the module.

**MrShorty** · 09-26-2012, 12:36 PM

I don't know how that many cases will effect performance.

There might be -- I'm not sure of your reasons for storing these values in constants. If I were doing this, I might try storing the directory names in a lookup table in a spreadsheet or in an array (I don't think constants can store arrays. If I'm wrong someone correct me). Then you could use some kind of lookup statement to return the directory string. There might be performance penalties for building the array or referencing the worksheet, too, so I'm not sure if this is better or just different.

**rvasquez** · 09-26-2012, 12:48 PM

Try this instead

Location = "G:\Users\" & MyInput & "\"

**ttd** · 09-27-2012, 11:44 AM

This macro is inside an add-in and I run it on individual client's spreadsheets so I cannot house the file locations string inside a central spreadsheet because I run this macro on tons of indivdual spreadsheets. Each client has a performance report saved in their own client folder. I open each file separately & audit the data then run the macro to save a file verison as a pdf.

Each client's folder location is unique so I cannot use Location = "G:\Users\" & MyInput & "\"

I finished entering all the file locations to the macro & it run quick so all good.

Thanks for your help!