Hi all,
Needing urgent help please to put together a VBA code that will do the following, assisting with speeding up workflow by removing time consuming manual editing.
Currently we export from our software a .txt file that contains data relevant to multiple points we have modeled. The data in the .txt file is separated by a comma, please refer to the attached screenshot. However it is not a .csv file format. Also if .txt file is opened in Excel all data is in column A only.
What we need is a VBA code that will open a window to locate the .txt file that contains this information.
Then once the file is selected through the window and open / ok is pressed to close the window, the VBA code will do the following:
1. Delete the top row of text in the selected file.
2. Search the data in the .txt file after the 4th comma in each row and group the data based on the text found, refer to the pink column in attached screenshot.
3. For each "group", for example all rows with "GRIDS", we need the code to create a .csv file in the same location as the selected .txt file, which contains only the rows with "GRIDS".
4. The code then needs to do the same for all other "groups" of text as per step 3 above. Essentially creating multiple separate .csv files for each "group". So looking at the attached screenshot there would be one .csv file for GRIDS, another .csv file for "STAGE 1 - SLAB POINTS" and so on. The file names will need to be the name of the "group".
5. In each .csv file created, a row of text will need to be added at the top. It will need to be: ID,X (m),Y (m),Z (m),Layer
6. Once all .csv files have been created, the original selected .txt file will need to be shifted into a new created folder in the same location, named "_superceded", if there is already a folder in the location named _superceded, no need for the code to try create a second folder, just move the .txt file into the existing folder.
Example .txt file can be downloaded here: Dropbox
Thanks guys
Needing urgent help please to put together a VBA code that will do the following, assisting with speeding up workflow by removing time consuming manual editing.
Currently we export from our software a .txt file that contains data relevant to multiple points we have modeled. The data in the .txt file is separated by a comma, please refer to the attached screenshot. However it is not a .csv file format. Also if .txt file is opened in Excel all data is in column A only.
What we need is a VBA code that will open a window to locate the .txt file that contains this information.
Then once the file is selected through the window and open / ok is pressed to close the window, the VBA code will do the following:
1. Delete the top row of text in the selected file.
2. Search the data in the .txt file after the 4th comma in each row and group the data based on the text found, refer to the pink column in attached screenshot.
3. For each "group", for example all rows with "GRIDS", we need the code to create a .csv file in the same location as the selected .txt file, which contains only the rows with "GRIDS".
4. The code then needs to do the same for all other "groups" of text as per step 3 above. Essentially creating multiple separate .csv files for each "group". So looking at the attached screenshot there would be one .csv file for GRIDS, another .csv file for "STAGE 1 - SLAB POINTS" and so on. The file names will need to be the name of the "group".
5. In each .csv file created, a row of text will need to be added at the top. It will need to be: ID,X (m),Y (m),Z (m),Layer
6. Once all .csv files have been created, the original selected .txt file will need to be shifted into a new created folder in the same location, named "_superceded", if there is already a folder in the location named _superceded, no need for the code to try create a second folder, just move the .txt file into the existing folder.
Example .txt file can be downloaded here: Dropbox
Thanks guys
