Need Excel VBA macro to fetch data from notepad

Y

yourskarthik

New Member
Joined
Dec 30, 2009
Messages
6
I have different notepads say notepad1, 2 3 and so on. I have to import data from notepads to excel..

All the notepads are stored in a directory (common folder) after importing data from first notepad, the code has to calculate the end of the row automatically and insert the data from notepad2 to next row and so on..

Also, I need a separate column to say from which notepad the data is fetched (say I should have column to display notepad1 for the data fetched from notepad1)

Appreciate if somebody can help me on this.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
Hi yourskarthik, and welcome to the board.

I have made the following presumptions: by "Notepads" you mean text files and the fields in the text files are seperated (deliminated) by commas.

In the code below you will have to edit these variables to meet your needs.

Code: 
[COLOR=green]'---------------------[/COLOR]
[COLOR=green]'----- EDIT THIS -----[/COLOR]
[COLOR=green]'---------------------[/COLOR]
[COLOR=darkblue]Const[/COLOR] myPath = "C:\Temp\"
[COLOR=darkblue]Const[/COLOR] delim = ","

The code reads all text files in the directory and stores their values in an array. Once all the text files have been read the array is output to "Sheet1" on the workbook containing this code.

Press ALt + F11 to launch the VBE Editor
Click Insert => Module
Copy and Paste the code into the module
Run the code (F5)
Code: 
[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]
 
[COLOR=green]'---------------------[/COLOR]
[COLOR=green]'----- EDIT THIS -----[/COLOR]
[COLOR=green]'---------------------[/COLOR]
[COLOR=darkblue]Const[/COLOR] myPath = "C:\Temp\"
[COLOR=darkblue]Const[/COLOR] delim = ","
 
[COLOR=darkblue]Sub[/COLOR] MergeMultipleTextFiles()
    [COLOR=darkblue]Dim[/COLOR] myFile [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]        [COLOR=green]'filename[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] strRecord [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]     'record string
    [COLOR=darkblue]Dim[/COLOR] arrRecord()             [COLOR=green]'record array[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] fNum [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]         'free file number
    [COLOR=darkblue]Dim[/COLOR] RowCounter [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]      [COLOR=green]'row counter[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]               'loop variable
 
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] errHandler
    myFile = Dir(myPath & "*.txt")
 
    RowCounter = 0
    [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] myFile <> ""
        fNum = FreeFile
        [COLOR=darkblue]Open[/COLOR] myPath & myFile [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Input[/COLOR] [COLOR=darkblue]As[/COLOR] #fNum
 
        [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] [COLOR=darkblue]Not[/COLOR] EOF(fNum)
            [COLOR=green]'read in the row into the record variable[/COLOR]
            Line [COLOR=darkblue]Input[/COLOR] #fNum, strRecord
            [COLOR=green]'add the filename to the record variable[/COLOR]
            strRecord = myFile & delim & strRecord
 
            RowCounter = RowCounter + 1
            [COLOR=green]'resize the record array[/COLOR]
            [COLOR=darkblue]ReDim[/COLOR] [COLOR=darkblue]Preserve[/COLOR] arrRecord(1 [COLOR=darkblue]To[/COLOR] RowCounter)
            arrRecord(RowCounter) = Split(strRecord, delim)
        [COLOR=darkblue]Loop[/COLOR]
 
        [COLOR=darkblue]Close[/COLOR] #fNum
        [COLOR=green]'get the next file in the directory[/COLOR]
        myFile = Dir()
    [COLOR=darkblue]Loop[/COLOR]
    [COLOR=green]'[/COLOR]
    '------------------
    [COLOR=green]'----- OUTPUT -----[/COLOR]
    [COLOR=green]'------------------[/COLOR]
    [COLOR=darkblue]With[/COLOR] ThisWorkbook.Sheets("Sheet1").Range("A1")
        [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] RowCounter
            .Offset(i - 1).Resize(, [COLOR=darkblue]UBound[/COLOR](arrRecord(i)) + 1).Value = arrRecord(i)
        [COLOR=darkblue]Next[/COLOR] i
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
errExit:
    [COLOR=green]'close all open text files[/COLOR]
    Reset
    [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
errHandler:
    [COLOR=green]'error message goes here[/COLOR]
    [COLOR=darkblue]Resume[/COLOR] errExit
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
Upvote 0
Hello Bertie,

Thanks for providing me the code.
But while fetching the values to the excel sheet, it shows with special characters/extra spaces.


Not able to attach the files here..
 
Upvote 0
Add this Line
Code: 
            [COLOR=green]'read in the row into the record variable[/COLOR]
            Line [COLOR=darkblue]Input[/COLOR] #fNum, strRecord
            [COLOR=red]Debug.Print "Record Contents=" & sRecord[/COLOR]
This will output the contents of sRecord to the Immediate Window at the bottom of the screen. Click View => Immediate Window if it is not visible. Copy and paste this output back here.
 
Upvote 0
Could you not use Data>Get External Data...Import Text File...?

If that works when you do it manually you could record a macro to get code to do it.

Try it with a couple of these text files and see how it goes.:)
 
Upvote 0
bertie said:
Hi yourskarthik, and welcome to the board.

I have made the following presumptions: by "Notepads" you mean text files and the fields in the text files are seperated (deliminated) by commas.

In the code below you will have to edit these variables to meet your needs.

Code: 
[COLOR=green]'---------------------[/COLOR]
[COLOR=green]'----- EDIT THIS -----[/COLOR]
[COLOR=green]'---------------------[/COLOR]
[COLOR=darkblue]Const[/COLOR] myPath = "C:\Temp\"
[COLOR=darkblue]Const[/COLOR] delim = ","

The code reads all text files in the directory and stores their values in an array. Once all the text files have been read the array is output to "Sheet1" on the workbook containing this code.

Press ALt + F11 to launch the VBE Editor
Click Insert => Module
Copy and Paste the code into the module
Run the code (F5)
Code: 
[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]
 
[COLOR=green]'---------------------[/COLOR]
[COLOR=green]'----- EDIT THIS -----[/COLOR]
[COLOR=green]'---------------------[/COLOR]
[COLOR=darkblue]Const[/COLOR] myPath = "C:\Temp\"
[COLOR=darkblue]Const[/COLOR] delim = ","
 
[COLOR=darkblue]Sub[/COLOR] MergeMultipleTextFiles()
    [COLOR=darkblue]Dim[/COLOR] myFile [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]        [COLOR=green]'filename[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] strRecord [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]     'record string
    [COLOR=darkblue]Dim[/COLOR] arrRecord()             [COLOR=green]'record array[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] fNum [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]         'free file number
    [COLOR=darkblue]Dim[/COLOR] RowCounter [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]      [COLOR=green]'row counter[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]               'loop variable
 
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] errHandler
    myFile = Dir(myPath & "*.txt")
 
    RowCounter = 0
    [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] myFile <> ""
        fNum = FreeFile
        [COLOR=darkblue]Open[/COLOR] myPath & myFile [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Input[/COLOR] [COLOR=darkblue]As[/COLOR] #fNum
 
        [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] [COLOR=darkblue]Not[/COLOR] EOF(fNum)
            [COLOR=green]'read in the row into the record variable[/COLOR]
            Line [COLOR=darkblue]Input[/COLOR] #fNum, strRecord
            [COLOR=green]'add the filename to the record variable[/COLOR]
            strRecord = myFile & delim & strRecord
 
            RowCounter = RowCounter + 1
            [COLOR=green]'resize the record array[/COLOR]
            [COLOR=darkblue]ReDim[/COLOR] [COLOR=darkblue]Preserve[/COLOR] arrRecord(1 [COLOR=darkblue]To[/COLOR] RowCounter)
            arrRecord(RowCounter) = Split(strRecord, delim)
        [COLOR=darkblue]Loop[/COLOR]
 
        [COLOR=darkblue]Close[/COLOR] #fNum
        [COLOR=green]'get the next file in the directory[/COLOR]
        myFile = Dir()
    [COLOR=darkblue]Loop[/COLOR]
    [COLOR=green]'[/COLOR]
    '------------------
    [COLOR=green]'----- OUTPUT -----[/COLOR]
    [COLOR=green]'------------------[/COLOR]
    [COLOR=darkblue]With[/COLOR] ThisWorkbook.Sheets("Sheet1").Range("A1")
        [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] RowCounter
            .Offset(i - 1).Resize(, [COLOR=darkblue]UBound[/COLOR](arrRecord(i)) + 1).Value = arrRecord(i)
        [COLOR=darkblue]Next[/COLOR] i
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
errExit:
    [COLOR=green]'close all open text files[/COLOR]
    Reset
    [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
errHandler:
    [COLOR=green]'error message goes here[/COLOR]
    [COLOR=darkblue]Resume[/COLOR] errExit
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
Click to expand...
Hello

Const delim = "," -- Are you getting the data delimited with comma and passing the values

arrRecord(RowCounter) = Split(strRecord, delim) here?

I need the data to be fetched delimited with TAB instead of comma.. can u help me here
 
Upvote 0
Change the line highlighted.

Code: 
[COLOR=#008000]'---------------------[/COLOR]
[COLOR=green]'----- EDIT THIS -----[/COLOR]
[COLOR=green]'---------------------[/COLOR]
[COLOR=darkblue]Const[/COLOR] myPath = "C:\Temp\"
[COLOR=red]Cons[/COLOR][COLOR=red]t delim = vbTab
[/COLOR]
 
Upvote 0
Hi,

this macro is great, but it doesn't seem to work for me. I'm using it to a Fixed Width text file (maybe thats the reason).

I stepped into the code and it always stops at

Do While myFile <> ""

and jumps right to

With ThisWorkbook.Sheets("Sheet1").Range("A1").

Any thoughts?
 
Upvote 0
Hi Arminel,

This line of code is not detecting a file. Is the extension correct?
myFile = Dir(myPath & "*.txt")
Click to expand...

Make sure the path to the file is correct and the path ends with a backslash "\"
Const myPath = "C:\Temp\"
Click to expand...
 
Upvote 0
Hey,

Yes that fixed it. now I'm having another problem, I'm trying to modify the code so that I can output specific values from the array to specific cells in excel.

Before I did anything, I just tried to view the values in the array by adding this line

PHP: 
MsgBox "array value:" & arrRecord(i)

so in the output block it looks like this

PHP: 
 With ThisWorkbook.Sheets("Sheet1").Range("A1")
        For i = 1 To RowCounter
            .Offset(i - 1).Resize(, UBound(arrRecord(i)) + 1).Value = arrRecord(i)
            MsgBox "array value:" & arrRecord(i)
        Next i
    End With

But I keep getting a "Type Mismatch error", why?
 
Upvote 0
You must log in or register to reply here.

Similar threads

Waving Not Drowning
  • Solved
Filter criteria range from two cells including in VBA macro
Replies
3
Views
116
Alex Blakenburg
A
J
  • Question
How can I create a list from a single layer of a directory?
Replies
6
Views
319
PeteWright
PeteWright
X
  • Solved
Converting % data point to different format for notepad extract
Replies
2
Views
422
xceej
X
F
  • Question
A macro for fetching data from a web page source
Replies
0
Views
402
fafas
F
L
  • Solved
Opening a non TXT with Notepad in excel
Replies
8
Views
607
LS2021
L

Forum statistics

Threads
1,215,132
Messages
6,123,227
Members
449,091
Latest member
jeremy_bp001

Share this page

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