Consulting

Results 1 to 3 of 3

Thread: VBA to match data in one file and copy data to an existing file

  1. #1

    VBA to match data in one file and copy data to an existing file

    OK, I have a macro that retrieves data from an Access database, then creates a separate spreadsheet for each item number and saves the spreadsheet. That works well. The problem I'm having is I have another spreadsheet to open that I want to copy data from and put it into the newly created spreadsheet.

    Example:
    New created saved filename - ABCD MM-YY.xlsx (Note: MM-YY will change each month)

    Existing spreadsheet - Intercompany.xlxs (Note: Data in column A matches only the name of the new created saved filename ABCD) Also, in this spreadsheet there are item numbers that do not have a matching saved file, those are ignored.

    Question: How do I match the data in column A (ABCD) in the Interconpany.xlxs spreadsheet with the filename ABCD MM-YY.xlxs?

    I am thinking that in order to eliminate the portion of the existing file ABCD MM-YY.xlxs to use LEFT and have that go until it finds a blank which will eliminate the MM-YY?

    I have been looking for a way to do this and am only getting bumfuzzled.

    Any help would be greatly appreciated.

  2. #2
    VBAX Expert
    Joined
    Oct 2012
    Posts
    726
    Location
    Yep.

    s = Left(s, InStr(s, " "))

    or
    s =Split(s, " ")(0)
    or
    s =Left(s, Len(s) - Len(" MM-YY.xlsx"))
    or
    s =Mid(s, 1, InStrRev(s, " "))

    etc

  3. #3
    Thanks John!

    As I am just beginning VBA I was unaware of InStr.

    Thanks again!

Posting Permissions

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