Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Location Based on Cell Reference
I have a worksheet cell (say a1) with values such as Paul, David, Mark etc.
What I am struggling with is to develop a macro which will use the data in that cell to determine the location the file will be saved to when the macro is run. i.e If a1= Paul, the file location will be F:/Paul's Directory/Filename.xls If a1 = Mark, the file location will be F:/Mark's Directory/Filename.xls Any suggestions would be appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Location Based on Cell Reference
Hi Monk,
In a standard module, try something like: '=========== Public Sub aTester() Dim WB As Workbook Dim SH As Worksheet Dim Rng As Range Dim arrNames As Variant Dim arrFolders As Variant Dim Res As Variant Dim sStr As String Const sName As String = "MyFile" '<<=== CHANGE Const sElseFolder As String = _ "C:\Users\Other\" '<<=== CHANGE arrFolders = VBA.Array("C:\Users\Paul\", _ "C:\Users\Mark\", _ "C:\Users\Joe") '<<=== CHANGE arrNames = VBA.Array("Paul", _ "Mark", _ "Joe") '<<=== CHANGE Set WB = ActiveWorkbook '<<=== CHANGE With WB Set SH = .Sheets("heet1") '<<=== CHANGE Set Rng = Sh.Range("A1") '<<=== CHANGE Res = Application.Match(Rng.Value, arrNames, 0) If IsError(Res) Then sStr = sElseFolder Else sStr = arrFolders(Res - 1) End If .SaveAs Filename:=sStr & .Name & ".xls", _ FileFormat:=xlWorkbookNormal End With End Sub '<<=========== Note that the constant sElseFolder value is provided to allow for the case in which an unrecognised (or empty) value is enterd in the cell of interest. As an alternative, you might consider replacing the arrays with a Select Case construct - see VBA help for details, or post back. --- Regards. Norman "Monk" wrote in message ... I have a worksheet cell (say a1) with values such as Paul, David, Mark etc. What I am struggling with is to develop a macro which will use the data in that cell to determine the location the file will be saved to when the macro is run. i.e If a1= Paul, the file location will be F:/Paul's Directory/Filename.xls If a1 = Mark, the file location will be F:/Mark's Directory/Filename.xls Any suggestions would be appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Location Based on Cell Reference
Thanks Norman
I was hoping there may be something less complex. The following code is part of an existing module and I was hoping it could pick up the value of cell A1 (i.e say value = Paul) and enter this in the directory so it would save as F:\Home\Paul\Trading\2.Draft Trades.xls. If A1 had a value of Mike it would save as F:\Home\Mike\Trading\2.Draft Trades.xls. Can the below be modified simply? fileSaveName = Application.GetSaveAsFilename("F:\Home\Cell A1\Trading\2.Draft Trades.xls", _ "Excel Files (*.xls), *.xls") "Norman Jones" wrote: Hi Monk, In a standard module, try something like: '=========== Public Sub aTester() Dim WB As Workbook Dim SH As Worksheet Dim Rng As Range Dim arrNames As Variant Dim arrFolders As Variant Dim Res As Variant Dim sStr As String Const sName As String = "MyFile" '<<=== CHANGE Const sElseFolder As String = _ "C:\Users\Other\" '<<=== CHANGE arrFolders = VBA.Array("C:\Users\Paul\", _ "C:\Users\Mark\", _ "C:\Users\Joe") '<<=== CHANGE arrNames = VBA.Array("Paul", _ "Mark", _ "Joe") '<<=== CHANGE Set WB = ActiveWorkbook '<<=== CHANGE With WB Set SH = .Sheets("heet1") '<<=== CHANGE Set Rng = Sh.Range("A1") '<<=== CHANGE Res = Application.Match(Rng.Value, arrNames, 0) If IsError(Res) Then sStr = sElseFolder Else sStr = arrFolders(Res - 1) End If .SaveAs Filename:=sStr & .Name & ".xls", _ FileFormat:=xlWorkbookNormal End With End Sub '<<=========== Note that the constant sElseFolder value is provided to allow for the case in which an unrecognised (or empty) value is enterd in the cell of interest. As an alternative, you might consider replacing the arrays with a Select Case construct - see VBA help for details, or post back. --- Regards. Norman "Monk" wrote in message ... I have a worksheet cell (say a1) with values such as Paul, David, Mark etc. What I am struggling with is to develop a macro which will use the data in that cell to determine the location the file will be saved to when the macro is run. i.e If a1= Paul, the file location will be F:/Paul's Directory/Filename.xls If a1 = Mark, the file location will be F:/Mark's Directory/Filename.xls Any suggestions would be appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Location Based on Cell Reference
Hi Monk,
Perhaps try: '========= Option Explicit Public Sub Tester() Dim WB As Workbook Dim SH As Worksheet Dim Rng As Range Dim aStr As String Dim sPath As String Dim FName As String Const sStr As String = "F:\Home\" Const sStr2 = "\Trading\2.Draft Trades.xls" Set WB = ThisWorkbook Set SH = WB.Sheets("Sheet2") '<<==== CHANGE Set Rng = SH.Range("A1") '<<==== CHANGE If Not IsEmpty(Rng.Value) Then aStr = Rng.Value FName = sStr & aStr & sStr2 ThisWorkbook.SaveAs Filename:=FName, _ FileFormat:=xlWorkbookNormal Else 'Your code to handle misssing data, e.g.: MsgBox Prompt:="Your message", _ Buttons:=vbCritical, _ Title:="Problem" End If End Sub '<<========= --- Regards. Norman "Monk" wrote in message ... Thanks Norman I was hoping there may be something less complex. The following code is part of an existing module and I was hoping it could pick up the value of cell A1 (i.e say value = Paul) and enter this in the directory so it would save as F:\Home\Paul\Trading\2.Draft Trades.xls. If A1 had a value of Mike it would save as F:\Home\Mike\Trading\2.Draft Trades.xls. Can the below be modified simply? fileSaveName = Application.GetSaveAsFilename("F:\Home\Cell A1\Trading\2.Draft Trades.xls", _ "Excel Files (*.xls), *.xls") |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Location Based on Cell Reference
Thanks Norman. Perfect.
"Norman Jones" wrote: Hi Monk, Perhaps try: '========= Option Explicit Public Sub Tester() Dim WB As Workbook Dim SH As Worksheet Dim Rng As Range Dim aStr As String Dim sPath As String Dim FName As String Const sStr As String = "F:\Home\" Const sStr2 = "\Trading\2.Draft Trades.xls" Set WB = ThisWorkbook Set SH = WB.Sheets("Sheet2") '<<==== CHANGE Set Rng = SH.Range("A1") '<<==== CHANGE If Not IsEmpty(Rng.Value) Then aStr = Rng.Value FName = sStr & aStr & sStr2 ThisWorkbook.SaveAs Filename:=FName, _ FileFormat:=xlWorkbookNormal Else 'Your code to handle misssing data, e.g.: MsgBox Prompt:="Your message", _ Buttons:=vbCritical, _ Title:="Problem" End If End Sub '<<========= --- Regards. Norman "Monk" wrote in message ... Thanks Norman I was hoping there may be something less complex. The following code is part of an existing module and I was hoping it could pick up the value of cell A1 (i.e say value = Paul) and enter this in the directory so it would save as F:\Home\Paul\Trading\2.Draft Trades.xls. If A1 had a value of Mike it would save as F:\Home\Mike\Trading\2.Draft Trades.xls. Can the below be modified simply? fileSaveName = Application.GetSaveAsFilename("F:\Home\Cell A1\Trading\2.Draft Trades.xls", _ "Excel Files (*.xls), *.xls") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Location Reference | Excel Discussion (Misc queries) | |||
Creating a linked file based on variable location reference | Excel Discussion (Misc queries) | |||
Cell Reference Location | Excel Discussion (Misc queries) | |||
Reference cell location | Excel Worksheet Functions | |||
Cell Reference to a location | Excel Programming |