Parse Part of File name and paste into excel

**rizmomin** · 08-14-2010, 11:12 AM

I am attaching txt file and excel file for your referance.
I am using VB macro (please look into Module1 of excel file) to Parse data from txt file and transfer into excel sheet.

Its working great, however i would like to add the date and Invoice # which is part of the txt
file to be pasted in column I and J as i have shown it manually in excel sheet cell I2,J2,...I3,J3...

eg : 2010-05-25-Asheer-CDI#455665 is the name of the txt file...
from here i would like to extract date (2010-05-25) and put it in column I
extract Invoice # (455665) and put it in column J

Also as you can see in VB code :

Set oFS = fso.OpenTextFile("C:/2010-05-25-Asheer-CDI#455665.txt", ForReading, False)

I would like to see if it is possible to manually enter the file name for reading by asking :

Please enter the location of the file to be read...and browse to the file..

Please let me know if you have any question.

Thank you...

Riz..

**gmahlert** · 08-15-2010, 02:20 PM

First I added GetOpenFilename instead of using a hardcoded filename.

    sFileType = "Text files (*.txt),*.txt"
    sFilename = Application.GetOpenFilename(sFileType)
    
    If sFilename = False Then
        MsgBox ("File Failed to open")
        Exit Sub
    End If

Then I changed the import routine from a simple IF THEN structure
into a SELECT CASE structure so the invoice data could be added.

Tested and working in Excel 2003 with the text file you provided.

**rizmomin** · 08-15-2010, 02:32 PM

Hello Gordon:

Thanks fro your help.
I am not getting the result..
I run the macro and it is opening window to choose the file...
i selected text file but nothing happens..and giving error "Failed to open the file"
Please help

Thank U
Riz Momin

**rizmomin** · 08-15-2010, 02:43 PM

Hi Gordon:

Please put the text file into C:
Now run the VB code and see what happens..

Thanks
Riz

**gmahlert** · 08-15-2010, 05:12 PM

Hello Riz

I just tested the VBA program again. My copy is working properly. At first glance it does seem like it's not doing anything, because the program is silent and very quick. Also the original program code adds the new data to the bottom of the spreadsheet out of sight.

Sheet1.UsedRange.Rows.Count

That would usually work just fine, but while I was testing the VBA code, I added a few hundred rows of test data to sheet 1, and instead of deleting the rows, I used clear contents. Sorry about that. Excel was adding the new rows way down at row 300 or so.
To fix the problem, I just deleted all the extra rows The next time you run the macro, the new data will be added at row 67.

I also modified the program to inform you that
invoice xxxxxx dated xx/xx/xx has been imported.

**gmahlert** · 08-15-2010, 05:17 PM

Ciao Riz.
BTW, What version of Excel are you running ?

**gmahlert** · 08-15-2010, 05:22 PM

Hello Riz
I was wondering about something. Are you actually clicking on the file to choose and open it ? or are you typing the file name instead ?

**rizmomin** · 08-15-2010, 05:54 PM

Hello:
I am clicking on the file to choose and open it.
Also tried by typing also...
Using Excel 2003...

I will try again and let you know soon...
Thank u so much for your help..

Riz

**rizmomin** · 08-15-2010, 06:12 PM

Hello Gordon:

This works great now....
i need to ask more favor, please...
Please open txt file and last page would have

Ship/Handling Tax
4.69 3.71

I would like these values added after the last line
Column E F
Ship/Handling 4.69
Tax 3.71

On separte sheet i would like the Summary as follows:

Date Inv # Total ($)

Would you please see if this is possible...

Thank U so much for your help..

Riz