getting string/data from notepad

vidge1111

New Member
Joined
May 4, 2010
Messages
14
i have a notepad that have data that i would like to use in excel.
I have read that you can open a notepad in excel with vba and that you can copy contents of the notepad into a string/range. is that possible?

I want to create a macro that will help me open a target file (open as notepad), maybe copy contents to a string.
Because what I really want to do is search through the contents of the text file, and copy a certain string.

the text file contains such data:(example)

MIDlet-Name: Helloword
MIDlet-Version: 1.1.0
MIDlet-Vendor: MyHouse

I want to be able to get the value for "version" or 1.1.0 and copy it to a cell.

Any inputs will be really appreciated. thanks!
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
There is no need to open the file in notepad. You may access it directly using one of the native file commands. See Open, Close, Get, Put, Input #, Write, Print # in VBA help. You may also wish to consider the Scripting Runtime library for simple text files.
 
Upvote 0
problem is, I am new to excel vba.. maybe someone can enlighten me with this topic. thanks :)

There is no need to open the file in notepad. You may access it directly using one of the native file commands. See Open, Close, Get, Put, Input #, Write, Print # in VBA help. You may also wish to consider the Scripting Runtime library for simple text files.
 
Upvote 0
You are best to gain some familiarity with the built in helps, attempt a code solution, and then post any problems you run into. I don't have much to work with at this point.

Is this the actual data in a single file?
MIDlet-Name: Helloword
MIDlet-Version: 1.1.0
MIDlet-Vendor: MyHouse

Will "Version" only be listed once in any single file?
 
Upvote 0
Sorry for the lack of details.

The single file may contain more lines of data.

and yes, the word version will only appear once in the text file.

You are best to gain some familiarity with the built in helps, attempt a code solution, and then post any problems you run into. I don't have much to work with at this point.

Is this the actual data in a single file?
MIDlet-Name: Helloword
MIDlet-Version: 1.1.0
MIDlet-Vendor: MyHouse

Will "Version" only be listed once in any single file?
 
Upvote 0
Here is a quick and dirty method to parse your file and retrieve specific items based upon the structure you have provided in your example.

To use the function from a worksheet cell, use the following syntax with the correct path to your file. Else you can use the example sub.

=GetItem("MIDlet-Version:","C:\Users\TJS\Desktop\text.txt")

Code:
Sub Example()
    Dim FileName As String
    
    FileName = Application.GetOpenFilename
    
    MsgBox GetItem("MIDlet-Version:", FileName)
    MsgBox GetItem("MIDlet-Vendor:", FileName)
End Sub

Function GetItem(Label As String, FileName As String) As String
    Dim Text As String
    
    Open FileName For Input As #1
        Do Until EOF(1)
            Line Input #1, Text
            If InStr(Text, Label) Then
                GetItem = CStr(Trim(Split(Text, ":")(1)))
                Close #1
                Exit Function
            End If
        Loop
    Close #1
    
End Function
 
Upvote 0
I got a code from the web that lets me choose the directory where the files are located.

I modified a code i created before so i can set a column as my range of data, the code then goes through each cell of the said column, using each cell as a filename.

Problem is the code only opens one file regardless how many there are in the said column. any inputs on this?

Code:
Sub fileloop()
    Dim MyDir As String
    Dim strPath As String
    Dim vaFileName As Variant
    Dim rngSrc As Range
    Dim NumRows As Integer
    Dim ThisRow As Integer
    Dim ThatRow As Integer
    Dim FirCol As Integer
    Dim BookA As Worksheet
    
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then
            txt2 = .SelectedItems(1)
        End If
    End With
    
    
    MyDir = txt2
    strPath = MyDir
    Set rngSrc = ActiveSheet.Range("S2:S200") 'list of files I want opened
    NumRows = rngSrc.Rows.Count
    ThisRow = rngSrc.Row
    ThatRow = ThisRow + NumRows - 1
    FirCol = rngSrc.Column
    
For J = ThisRow To (ThatRow - 1) 'It doesnt loop through S2 to S200.why?
    If Cells(J, FirCol) > "" Then 'check if cells is empty
        With Application.FileSearch
        .NewSearch
        .LookIn = strPath
        .SearchSubFolders = False
        .filename = Cells(J, FirCol) ' Search for cell-specific file
        Open_File
        End With
    End If
Next J
End Sub


Private Sub Open_File()

With Application.FileSearch
        If .Execute > 0 Then

           For Each vaFileName In .FoundFiles
                'open the workbook
               Workbooks.Open vaFileName
        
                'perform something here
               With ActiveWorkbook
                    '.Worksheets("Sheet1")
                    'maybe I can get the version here?
                    '.Close
                End With
            Next
        End If
   End With
End Sub
 
Upvote 0
I don't know? Maybe you could contact the person who wrote it. I'm out of this thread.
 
Upvote 0

Forum statistics

Threads
1,215,001
Messages
6,122,648
Members
449,092
Latest member
peppernaut

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top