VBA to import .txt files

[John5]

Hello everyone

My company has a excel document that keeps records. I would like to automate this process and the first step is to import .txt documents into excel.

What I would like is help with the VBA that would automatically pull the info from the .txt files and put everything in it own cell that is separated by a space. The .txt files that are being imported will all be in one folder. It is possible that info in the .txt files will be updated here and there and possibly completely new info would be added so I would like the excel sheet to repopulate the info every time it is opened.

The next part is where I'm not sure how to proceed. Whether to have each .txt file in it own sheet or all in one sheet. Below is a example of the information in the txt file

sh ip int br
Interface IP-Address OK? Method Status Protocol
GigabitEthernet0/0.1999 1.1.1.1 YES NVRAM administratively down down
GigabitEthernet0/0.2002 2.2.2.22 YES NVRAM up up
GigabitEthernet0/1.1 3.3.3.3 YES NVRAM up up
GigabitEthernet0/1.2 4.4.4.4 YES NVRAM up up
GigabitEthernet0/1.10 5.5.5.5 YES NVRAM up up
GigabitEthernet0/1.11 6.6.6.6 YES NVRAM up up
GigabitEthernet0/1.200 7.7.7.7 YES manual administratively down down
GigabitEthernet0/1.210 8.8.8.8 YES NVRAM up up
Serial0/0/0 9.9.9.9 YES NVRAM down down
Tunnel500 10.10.10.10 YES manual up up
Test-RTR2821#
ex

Not all .txt files will have as many lines some only 5.

The over all Goal is to pull the name in the case above that is "Test-RTR2821#" then certain ip address along side it in a master sheet of sorts that would be used as a resource. See below.

Company name Transport Voice Data
Test-RTR2821 2.2.2.22 5.5.5.5 3.3.3.3
xxxxxx
xxxxx
xxxxx

....

What I don't know is once info has been imported would it be easier to pull the info out of one sheet or individual sheets that I need. Im also not sure how since non of the info will always be in the same cell.

side note I will have around 300 .txt documents to import

Any help with this would be greatly appreciated as I have been trying to figure this out using other VBA resources but have had no luck.

 

[John5]

Longer then 6 but I am having issue finding that if stament. Is this it the one in Red?

If Len(StrBuFFer) > 6 Then
'All data is initially handled as text strings.
Tab1 = Application.WorksheetFunction.Find(vbTab, StrBuFFer, 1)
'Locates first Tab character in String
TempCol1 = Mid(StrBuFFer, 1, Tab1) 'Captures value
tab2 = Application.WorksheetFunction.Find(vbTab, StrBuFFer, Tab1 + 1)
'Locates next Tab in string
TempCol2 = Mid(StrBuFFer, Tab1, tab2 - Tab1)
tab3 = Application.WorksheetFunction.Find(vbTab, StrBuFFer, tab2 + 1)
TempCol3 = Mid(StrBuFFer, tab2, tab3 - tab2)
tab4 = Application.WorksheetFunction.Find(vbTab, StrBuFFer, tab3 + 1)
TempCol4 = Mid(StrBuFFer, tab3, tab4 - tab3)
tab5 = Application.WorksheetFunction.Find(vbTab, StrBuFFer, tab4 + 1)
TempCol5 = Mid(StrBuFFer, tab4, tab5 - tab4)
'tab6 = Application.WorksheetFunction.Find(vbTab, StrBuFFer, tab5 + 1)
'TempCol6 = Mid(StrBuFFer, tab5, tab6 - tab5)
'tab7 = Application.WorksheetFunction.Find(vbTab, StrBuFFer, tab6 + 1)
'TempCol7 = Mid(StrBuFFer, tab6, tab7 - tab6)
'tab8 = Application.WorksheetFunction.Find(vbTab, StrBuFFer, tab7 + 1)
'TempCol8 = Mid(StrBuFFer, tab7, tab8 - tab7)

 

[John5]

That was not what I was tying to do. Was trying to use the Code: box I have seen you use.

 

[SpillerBD]

That was not what I was tying to do. Was trying to use the Code: box I have seen you use.

Well it was close enough, but yes that is the If statement I referred to previously.

 

[John5]

Thank you and my first example was slightly incorrect there is no "ex" on the end and the show command is different. Below is accurate as to what I am importing. Just copy into a notepad make some change and create other text files and that all I'm doing to test currently. The IP address and name of device have been adjusted but for what we are currently working on that info is not relevant. I have 4 notepads im using to test the <acronym title="visual basic for applications">VBA</acronym> you gave me with no success the most I can do is get 1 imported and it strips the last cell which is the name of the device.

I do need a <acronym title="visual basic for applications">VBA</acronym> the pulls all the files from a folder I point it to (that will contain all the notepads text docs) every time I open the excel doc so that it will auto adjust if any changes are in the notepads. I have found out how to connect a notepad to a excel sheet and it auto adjusts when I make a change to the notepad and reopen the excel file but this is for only one text file. I did this using the Data> from text way not with a <acronym title="visual basic for applications">VBA</acronym> but i did record it and will send you the macro in case it helps. I don't have the knowledge to use that macro to make it look at a folder and import the text files to a new sheet per text file. Also when it import to a new sheet have them just use sheet1 sheet2 sheet3 sheet4 .... so on.

Here is the example doc save off with a file name like this "Cool_Office_Lab.DeviceOutput.20160209" everything else stays the same and the "Lab" part can be replaced with switch or router or AP. That is what I'm using to test with.

sh ip int br | ex unassigned
Interface IP-Address OK? Method Status Protocol
GigabitEthernet0/0.1999 1.1.1.1 YES NVRAM administratively down down
GigabitEthernet0/0.2002 2.2.2.22 YES NVRAM up up
GigabitEthernet0/1.1 3.3.3.3 YES NVRAM up up
GigabitEthernet0/1.2 4.4.4.4 YES NVRAM up up
GigabitEthernet0/1.10 5.5.5.5 YES NVRAM up up
GigabitEthernet0/1.11 6.6.6.6 YES NVRAM up up
GigabitEthernet0/1.200 7.7.7.7 YES manual administratively down down
GigabitEthernet0/1.210 8.8.8.8 YES NVRAM up up
Serial0/0/0 9.9.9.9 YES NVRAM down down
Tunnel500 10.10.10.10 YES manual up up
Test-RTR2821#

I found this link to another Mr.excel page with a <acronym title="visual basic for applications">VBA</acronym> that should do what I want but it doesn't work for me at all. Might be useful.

Importing Multiple Text Files at once


Here is the macros I recorded. Its based on the specific file name which would take awhile to duplicate and would need to be adjusted if a new text file was added which defeats the purpose of this excel document. I would rather it look at a folder and import all text doc in that folder.

Sub Macro1()
'
' Macro1 Macro
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;File directory here" _
, Destination:=Range("$A$1"))
.CommandType = 0
.Name = "Cool_Office_router.DeviceOutput.20160209"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

 

[vicedo]

Hi,

The link below should help out.

http://www.mrexcel.com/forum/excel-...heet-single-workbook-multiple-worksheets.html

 

[John5]

Thank you for this and if I knew more about VBA I might be able to change it to import a text file per work sheet each time excel file was opened. But I do not and that is what I am in need of.

 

[John5]

I am running excel 2013 which I believe is why some of the VBA's that I have found seem to work for others but does not work for me. Plus I don't know how to change or manipulate a VBA to do what I want.