importing text into excel

[mash1k]

Hello, I'm trying to write a macro in excel that via a button click, will open a text file read it line by line, validate and calculate variables based on certain conditions and then display them on the spreadsheet.
I've written a previous macro that is based on opening the text file via importing it, deleting unwanted columns, entering formulas etc by using the record macro option. However, this is ok for small files, but I'm dealing with large files and this method is time consuming to process the information, it is also time consuming when I have added on a filter and am trying to filter out information via certain criteria.

your assistance is greatly appreciated

Mash1k

 

[VBAjedi]

It's difficult to picture exactly what you are attempting without seeing your existing code. Please post the relevant pieces of code.

It's possible that you may want the Open/Input/Close method. Read the help files on these three. Here's a little chunk of code that I keep on hand (the actual read loop is from the example in the help files):

Dim NewFileName As String
Do
   NewFileName = Application.GetOpenFilename(, , "Read what text file?")
   If NewFileName = "False" Then
      x = MsgBox("No file specified. Try again?", vbOKCancel)
      If x = 2 Then Exit Sub
   End If
Loop While NewFileName = "False"

Open NewFileName For Input As #1
Do While Not EOF(1)  ' Loop until end of file.
   Input #1, MyString, MyNumber  ' Read data into two variables.
   Debug.Print MyString, MyNumber   ' Print data to Debug window.
Loop
Close #1 ' Close file.

Again, you need to post your code for more specific guidance.

VBAjedi

 

[mash1k]

Hello VBAJedi, thx for replying, here is a bit more detail, the text file contains a product number (integer 7-8 digits),a date, 24hour clock time, a user(3 letters), a quantity, a minus or addition code(2 letters),an adjustment type(12 letters),and a document number(7 letters), if all of these do not appear on 1 line it is to be disregarded and the nextline should be read, there are going to be differing variables for certain times and dates and then these totals are to be placed in cells on a spreadsheet. I am going to see what I can do with the code you have given me.
Thanks for the help so far, I look forward to your reply.
Regards

Mash1k

 

[Siggy19]

Wierd as it sounds, if there are less that 65,000 rows in the file, it may be quicker to simply load the file into a spreadsheet and then filter out the unwanted rows.

Remember to then save the file before doing other manipulation since Excel does not reset the active cells until the file is saved. (For example, if you do <Control> and <End> it will not actually go to the last cell, because it does not realise that some earlier cells have been deleted.