+ Reply to Thread
Results 1 to 7 of 7

VBA Merge Files: Location Path as cell reference

  1. #1
    Registered User
    Join Date
    01-10-2018
    Location
    USA
    MS-Off Ver
    10
    Posts
    17

    VBA Merge Files: Location Path as cell reference

    Hello, I have some code that works to pull all files from a server location specified within the code.
    I would like to slightly change this data to pull the path from a cell reference (sheet1 cell A1) instead of within the code.
    When I run the code, nothing happens. I run the alternate code with the path in the coding, it works just fine.
    Reason being, several ppl work with this data and would like them to update a cell instead of changing a line of code monthly.
    Any help would be appreciated (btw: only pulling files in a folder starting with the work Comparative)
    Please Login or Register  to view this content.
    Last edited by Leith Ross; 01-24-2018 at 03:08 PM. Reason: Added Code Tags

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: VBA Merge Files: Location Path as cell reference

    Hello Kalyan46,

    When working with multiple workbooks or worksheets, it is best to assign these objects to object variables. This will help in keeping track of what your code is doing to what and where.

    Try this modified version of your macro and let me know the results.

    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    01-10-2018
    Location
    USA
    MS-Off Ver
    10
    Posts
    17

    Re: VBA Merge Files: Location Path as cell reference

    Perfect, thank you Leith for the quick response

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: VBA Merge Files: Location Path as cell reference

    Hello Kalyan46,

    You're welcome and welcome to the forum!

  5. #5
    Registered User
    Join Date
    01-10-2018
    Location
    USA
    MS-Off Ver
    10
    Posts
    17

    Re: VBA Merge Files: Location Path as cell reference

    One additional question, this works perfect when using our server location but if I want to use a sharepoint site, I am getting an error on this line
    Filename = Dir(FolderPath & "Comparative*.xls*")

    Inout on Cell A1:
    https://teams.connect.mycompany.com/sites/forecast

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: VBA Merge Files: Location Path as cell reference

    Hello kalyan46,

    I don't have access to a Sharepoint Intranet to test this but I think it will work.

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    01-10-2018
    Location
    USA
    MS-Off Ver
    10
    Posts
    17

    Re: VBA Merge Files: Location Path as cell reference

    the file is just spinning, when I click debug, it seems like it is hung up at line


    Set Wkb = Workbooks.Open(Filename:=FolderPath & Filename, ReadOnly:=True)

+ 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. change path for save location based upon text in a cell
    By wardy212 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-06-2016, 08:34 AM
  2. [SOLVED] Change file path name in VBA code based on files location
    By klunker in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-11-2014, 04:08 PM
  3. [SOLVED] Merge different Excel/CSV files into one with variable in path name
    By nikats in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-22-2013, 05:45 AM
  4. Import txt files but not being forced to select them from one location (path)
    By superbob in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-04-2011, 06:07 PM
  5. How to set GetOpenfile dialog start path to current open files path.
    By Michael Wise in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-03-2010, 02:24 PM
  6. File Path Reference to Location of Open Spreadsheet
    By R_S_6 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-01-2009, 11:09 AM
  7. Using a cell reference to determine path
    By SnowCom in forum Excel General
    Replies: 3
    Last Post: 08-19-2008, 08:21 AM

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