Opening & Reading file in notepad then pasting into excel using VBA

[DJ MC]

Hi,
ive been struggling over the last week to complete a task in VBA excel
and wondering if anyone out there can help!

I Have a window where the user selects a .GFF file, the location is
pasted into a text box and made into a variable, once the start button
it clicked, the .GFF file needs to be opened in notepad, this is where
the problems start. I've tried

Shell(Notepad.exe GFF_Location,1)

GFF_Location being the variable with the file location but this doesnt
work as it tries to open notepad and finds a file called GFF_Location
and when not found asks if i would like to create it?

After this file can be opened in notepad data needs to be read and
pasted into cells in a new excel worksheet. this is what it looks like
in the GFF file.

LIN1 000001
NAD2A
BV
NAD2B SULOJMANI R A MR
ATT2 11 PX274500A
ATT2 19 0950009
DTM2 182 20060430 102
DTM2 7 20060518 102
DTM2 166 2007 602
ALC1 4 466L

And this is what it needs to look like in excel

A B C
D
1 LIN1 NAD2A NAD2B ATT2 11
2 000001 BV SULOJMANI PX274500A

ect..ect,..


Any help would be much appreciated as im pulling my hair out lol.

 

[SeanC UK]

Hi,

I don't know much about GFF files, are they just text files? If so, you can
try opening directly from Excel using the GetOpenFilename method, the VBA
help is good for understanding this, you can make it look for only GFF files
(try opening the GFF file directly from Excel to see what you get).
Alternatively, once you have the file location you could use the OpenTextFile
method to open the file (this does not open it in Notepad, it just means that
Excel has the file open), and then use methods such as ReadLine to get the
content. Once you have read all the data in you can place it in the
spreadsheet as you want.

I don't know how much information will be available in Excel's MSDN pages on
reading from text files, but there will be good information in the VBS
scripting sections.

Both the above thoughts are based on avoiding having to use Notepad.
However, it is possible to do this, but probably wouldn't be as straight
forward.

Sean.

 

[Vergel Adriano]

Try it this way:

Shell "Notepad.exe " & GFF_Location, vbNormalFocus

If there is possibility that the file would not exist, you can check for
existence first with something like this:

If Dir(GFF_Location) <> "" Then
Shell "Notepad.exe " & GFF_Location, vbNormalFocus
Else
MsgBox GFF_Location & " file not found!"
End If

 

[DJ MC]

Thanks for the replys

Yes that code opened the variable perfectly thanks

im curious on this 'ReadLine' code, would this be able to read the
text in the notepad and paste contents into excel cells the way i need
it to? Also there are parts like MR
A B, these would need to be trimmed down to
just the characters and pasted into cells.
Thanks again.

 

[Vergel Adriano]

Sorry, I missed the part in your post where you said you wanted to read the
text file and paste into Excel. I couldn't clearly understand the logic
based on your sample data and expected output.. but it looks like you'll need
to code your own parsing logic and then paste into Excel. So, maybe
something like this could get you started. The code below will read the text
file one line at a time and then paste each line in column A of a new
sheet... you'll need to tweak the code so that you can parse each line and
put each piece in the right place.


Sub test()
Dim gff_location As String

Dim oFSO As Object
Dim ts As Object

Dim strLine As String
Dim lRow As Long

gff_location = "C:\sample.txt"
Set oFSO = CreateObject("Scripting.FileSystemObject")

lRow = 1
If oFSO.FileExists(gff_location) Then

'read the text file
Set ts = oFSO.OpenTextFile(gff_location)

'create a new worksheet
'the new sheet will become the active one
Sheets.Add

While Not ts.AtEndOfStream
strLine = ts.ReadLine

'you need to parse the line according to
'your logic first before
'putting them in the worksheet
'this sample just puts each line in column A

ActiveSheet.Range("A" & lRow).Value = strLine
lRow = lRow + 1
Wend

Else
MsgBox gff_location & " file not found!"
End If

End Sub

 

[DJ MC]

Thanks for the help it worked nicely and gave good grounds to carry on
with.

im now putting line contents into its cells no problem, its just
the .GFF file seems to always be on top of the excel sheet, is there
any way of putting this in the background and still reading from it?
i have a date checker in place which checks the date on a spacific
line, if the date in the file is less than the current date a yes/no
box appears, the user cannot see this box as the .GFF file is still as
the front of the screen.

Application.ScreenUpdating = False doesnt seem to do the trick

Thanks again!