Create vba file name from cell


  • Rather than hard code a file name to be used within vba. I want to type in filename in A13. Then use this filename for the "SourceFile" can this be done? I'm having no luck hacking at it?

    Dim wbkS As Workbook
    Dim SourceFile As String
    Dim DestinationFile As String

    SourceFile = "Series7_B.xlsm"

    Is it possible to prompt or open up a window and path to the file name?

    Monday, October 07, 2013 6:35 AM

All replies

  • Your question is not very clear. What does the cell have and what are you trying to create?


    Monday, October 07, 2013 6:38 AM
  • Yes it is possible. Let's say the file name is stored in Cell A13 of Sheet1 then you can use the below

    sPath = "C:\Folder\"
    SourceFile = sPath & wbkS.Sheets("Sheet1").Range("A13").Value

    Regarding your other question

    "Is it possible to prompt or open up a window and path to the file name?"

    Which path are you referring to? Not sure if you are referring to "Application.GetOpenFilename" or  "Application.GetSaveAsFilename"


    Monday, October 07, 2013 7:03 AM
  • That code should work :)

    Ok few questions

    1. Where are you running the code from? Within Excel? Automating Excel from some other application?

    2. The sPath values and the "Sheet1" are values given by me to explain the process. Did you replace them with your actual values?

    3. What is the value of Cell A13?


    Tuesday, October 08, 2013 5:22 AM
  • The problem is with this line of the code

    SourceFile = sPath & wbkS.Sheets("Update Checklist").Range("A13").Value

    because you are trying to use "wbkS" before it is initialized.

    I haven't checked the rest of the code. Change the above first and then we ill take it form there.


    Wednesday, October 09, 2013 5:31 AM