+ Reply to Thread
Results 1 to 3 of 3

Open an Excel File from within a macro in Excel 2007 from user input

Hybrid View

  1. #1
    Registered User
    Join Date
    11-05-2013
    Location
    Arlington, TX
    MS-Off Ver
    Excel 2007
    Posts
    23

    Open an Excel File from within a macro in Excel 2007 from user input

    As part of a project I am working on I take user input from list boxes, concatenate the input to create the path and file name to open and store that in a cell. I now am trying to open that file as part of a macro. Cell A21 contains this: =CONCATENATE("J:\Information\Rack Power Database\Programming\Deployed Racks\", B3, "\", B4, ".xlsm") . Any suggestions as to how to best accomplish this as I keep erroring out on the last line of code? Not sure if this needs to be identified as a value or if it should not be dimensioned as a string.

    Sub NewRackinCO()
    ' New Rack in CO
    ' This button allows you to add a new rack into a CO
    '
    '   Dimension the Variables
        Dim Cust As String
        Dim SiteName As String
        Dim RackPosNo As String
        Dim ACDCVolt As String
        Dim strPath As String
    '   Stores Input data as variables
        Sheets("New Rack in C.O.").Select
        Range("B3").Select
        Cust = ("B3")
        SiteName = ("B4")
        RackPosNo = ("B5")
        ACDCVolt = ("B6")
        strPath = ("A21")
    '   Opens the workbook "SiteName" in the "Cust" directory to add a new rack
        Workbooks.Open Filename:=strPath
    End Sub

  2. #2
    Forum Contributor
    Join Date
    10-13-2012
    Location
    Southern California
    MS-Off Ver
    Excel 2007
    Posts
    401

    Re: Open an Excel File from within a macro in Excel 2007 from user input

    Using Debug, step through your code, one line at a time, and stop just before the last line it crashes on. Put your cursor over the strPath variable to see the current value for that variable. I suspect it won't be quite what you think it should be, revealing your error.

  3. #3
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Open an Excel File from within a macro in Excel 2007 from user input

    Maybe:

    Sub NewRackinCO() 'WarrenSmiley
    ' New Rack in CO
    Sheets("New Rack in C.O.").Activate
    ' This button allows you to add a new rack into a CO
    '
    '   Dimension the Variables
        Dim Cust As String
        Dim SiteName As String
        Dim RackPosNo As String
        Dim ACDCVolt As String
        Dim strPath As String
    '   Stores Input data as variables
        Cust = Range("B3")
        SiteName = Range("B4")
        RackPosNo = Range("B5")
        ACDCVolt = Range("B6")
        strPath = Range("A21")
    
    '   Opens the workbook "SiteName" in the "Cust" directory to add a new rack
        Workbooks.Open FileName:=strPath
    End Sub
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Excel macro that can open up a website and input a user name and password.
    By glide2131 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-09-2014, 09:10 PM
  2. Replies: 0
    Last Post: 10-02-2013, 01:47 AM
  3. [SOLVED] Need code to run macro in excel every 15 minutes, but only if user has File open
    By Kanga1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-16-2013, 08:26 AM
  4. Replies: 10
    Last Post: 06-07-2012, 09:17 AM
  5. VBA for excel 2003. Open file dialog box, open 2007 file xlsx, continue with code
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-25-2011, 12:12 AM
  6. [SOLVED] Need to Save Excel XML as just Excel file for user to open in Excel 2000
    By Fatmosh in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-20-2005, 01:05 PM
  7. Need to Save Excel XML as just Excel file for user to open in Excel 2000
    By fatmosh@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-19-2005, 03:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1