Looking for a VBA solution to do the following:
*** user has a number (200-300) TXT files stored in a folder, each TXT file has multiple pages with details of client records for a particular retail store, and the store number appears on the first line of each page in the format of "Store# : #####". There are blank lines after each records, therefore many blank lines in each page. See sample #1 below.
1) Prompt user to select a folder, then import all TXT files found and merge into one sheet called "MergedSheet".
2) Delete all blank rows
3) Insert a new column (left-most) and perform a wild card search to look for the word "Store" in each row and, if found, copy of the number found after the text "Store# :" and place in the new column "A" of that respective row.
4) Perform TEXT-TO-COLUMN to column "B" (i.e. originally column "A" before steps #2)
For #2, I could find a lot of answers to delete blank rows but for some strange reasons, none of them worked for me (after running the macro, all the blank rows are still there despite no error from the macro). However, if I do a "text to column" first to divide up the rows, all the macros worked.
For #3, I can do a wild card search but don't know how to retrieve the number after the search word to place in column A.
For #4, I should be able to figure this out myself but would appreciate if this can be included in the solution as well
Appreciate help!
Sample TXT file layout (note that some records have one line while some others have multiple lines. Each record always starts with a "REF#" on the first line)
Sample Excel file after import/merge:
*** user has a number (200-300) TXT files stored in a folder, each TXT file has multiple pages with details of client records for a particular retail store, and the store number appears on the first line of each page in the format of "Store# : #####". There are blank lines after each records, therefore many blank lines in each page. See sample #1 below.
1) Prompt user to select a folder, then import all TXT files found and merge into one sheet called "MergedSheet".
2) Delete all blank rows
3) Insert a new column (left-most) and perform a wild card search to look for the word "Store" in each row and, if found, copy of the number found after the text "Store# :" and place in the new column "A" of that respective row.
4) Perform TEXT-TO-COLUMN to column "B" (i.e. originally column "A" before steps #2)
For #2, I could find a lot of answers to delete blank rows but for some strange reasons, none of them worked for me (after running the macro, all the blank rows are still there despite no error from the macro). However, if I do a "text to column" first to divide up the rows, all the macros worked.
For #3, I can do a wild card search but don't know how to retrieve the number after the search word to place in column A.
For #4, I should be able to figure this out myself but would appreciate if this can be included in the solution as well
Appreciate help!
Sample TXT file layout (note that some records have one line while some others have multiple lines. Each record always starts with a "REF#" on the first line)
Sample Excel file after import/merge:
