Need Excel VBA macro to fetch data from notepad

[yourskarthik]

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.

 

[justine06]

to follow on this thread on getting data on notepad, how to make a auto back up data from notepad, like from path "c:\temp\*.txt" when it detects the data on notepad change it will make a back up and save to "c\back up data" with date/time stamp/hostname stamp.
Is it possible to make?

hope you will understand my english :D

 

[bertie]

Wow, I wonder what I was doing in July 2010 that I didn't respond to the original poster?

Justine
Detect the data on Notepad (text file) has changed?
You would need to process the file.
You would need something to compare it to.
It is possible, but depending on the file size, number of files, it could prove a laborious task.

A simpler approach may be to keep track of when the file was last modified, if it differs, back up the file, update last modified date.

It would depend on how the text files are generated.
Berite

 

[Norie]

Bertie

You did respond to the OP, you were the last person to post to the original thread.

 

[justine06]

Option Explicit

Const myPath = "c:\temp
Const delim = " "
Const delim = vbTab

how to combine this two delim code, It always says "Complie Error Ambiguous Name Detected: Delim"

 

[bertie]

The ambiguity is that you have two variables with the same name, "delim". I used this variable name to denote the text file deliminator, i.e., comma, space, tab, etc.

Off the top of my head, if you want to allow the user to select the type of deliminator I suppose you could use an input box, then use a Select Case statement to determine the user's choice. Something like:

Sub test()
   Dim myDeliminator  As Long
   Dim delim As String
   
   myDeliminator = Application.InputBox("Select deliminator:" & vbCrLf & _
                                       "1. Tab" & vbCrLf & _
                                       "2. Space" & vbCrLf & _
                                       "3. Comma", Type:=1)
               
   Select Case myDeliminator
      Case Is = 1
         delim = vbTab
      Case Is = 2
         delim = " "
      Case Else
         delim = ","
   End Select


End Sub

 

[justine06]

Hi Bertie,

thanks for the code that you give, I try to put and run the module but a window pop up and ask to choose one of the three selection, I need to run only the deliminator Tab and Space at the same time so I can get the specific data that I want to get. and how I can insert it on the whole module.

 

[bertie]

Try recording a macro and follow step 2/3 in the link below.
https://support.office.com/en-nz/article/Text-Import-Wizard-c5b02af6-fda1-4440-899f-f78bafe41857

Pay particular attention to the option: Treat consecutive delimiters as one