macro to open text file into spreadsheet

lubbs65

Board Regular
Joined
Jun 1, 2011
Messages
80
I currently have a spreadsheet that has a few graphs that takes data from specific points. I have a text file that can currently be opened, then be copy pasted into that current spreadsheet. Once pasted, all of the formats from the text file are correct. I'm trying to create a macro that will paste it into the currently opened spreadsheet.

i've tried code like http://www.pcreview.co.uk/forums/macro-copy-daily-text-file-t978817.html the last post, but it didn't end up working. An error got flagged on his second line.

I created one macro that didn't work. it opened the text file, but also opened a new excel file and put the text into that file rather than the specific spreadsheet in the current file I wanted.

does anyone know of a macro that would work?
I'm not exactly an excel guru so any help is greatly appreciated.
 
Last edited:

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
In my file "excel_practice" my personal macro didn't do what I wanted. i hit "record macro" in developer, then went to open, found the text file i wanted lets call it "practice.txt" (the same file and location that it will always be). The file opened in another excel window called "practice". I then copied the contents of "practice" into sheet 1 of "excel_practice". Then stopped the macro from recording.

When i then execute the macro, the practice.txt again opens in a new excel file called practice. Then it stops. Nothing is copied over to sheet 1 of excel_practice which is especially what I need. In the end of the day, I want to hit one macro that opens the text file and puts the contents into sheet 1 of "excel_practice".
 
Upvote 0
I currently have a spreadsheet that has a few graphs that takes data from specific points. I have a text file that can currently be opened, then be copy pasted into that current spreadsheet. Once pasted, all of the formats from the text file are correct. I'm trying to create a macro that will paste it into the currently opened spreadsheet.

i've tried code like http://www.pcreview.co.uk/forums/macro-copy-daily-text-file-t978817.html the last post, but it didn't end up working. An error got flagged on his second line.

I created one macro that didn't work. it opened the text file, but also opened a new excel file and put the text into that file rather than the specific spreadsheet in the current file I wanted.

does anyone know of a macro that would work?
I'm not exactly an excel guru so any help is greatly appreciated.

Try this:
Code:
Sub ImportData()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\xxx\name_of_file.txt", Destination:=Range("$A$1"))
        .Name = "name_of_file.txt"
        .FieldNames = False
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(2)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
End Sub

You will need to modify the 2nd & 3rd lines to suit your needs.

C:\xxx\name_of_file.txt", Destination:=Range("$A$1"))

xxx = file directory
name_of_file.txt = name of your text file
$A$1 = is cell where you want the data imported
 
Upvote 0
Try this:
Code:
Sub ImportData()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\xxx\name_of_file.txt", Destination:=Range("$A$1"))
        .Name = "name_of_file.txt"
// im cutting a lot of lines out in the code so my post isn't 1 mile long

End Sub
You will need to modify the 2nd & 3rd lines to suit your needs.

C:\xxx\name_of_file.txt", Destination:=Range("$A$1"))

xxx = file directory
name_of_file.txt = name of your text file
$A$1 = is cell where you want the data imported

It didn't compile. Here's what I tried:
Code:
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+Shift+K
'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\xxxxx\Desktop\practice code\practice\practice\output.txt", Destination:=Range("R1C1"))
        .Name = "output.txt"
        .FieldNames = False
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(2)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False

End Sub
The beginning "Sub Macro2()" is highlighted which doesn't help at all.

I changed the directories which I know are correct. The space in "practice code" shouldn't at all matter. Originally i tried $A$1 which failed so i put in
R1C1 incase that would make a difference since that is what my cell is actually labeled as. This too failed.

Andrew, I tried data->get external data-> from text
then selected the file. there were some problems that have orisin
1) if this is my text file:
this is a really long line that I want the cell to end at the 'r' in really rather than the last word
2
3
4
5

the first column ends at the end of "word" therefore making all of the first column way to wide. is there a way to make it simply end earlier? I have 16 more columns to account for by the end of the text file so having some super-wide columns is a hassle.

problem 2:
everything that was once on the spreadsheet isn't just pasted over. instead it is all shifted right. I dont want this adjustment because i have graphs that are supposed to grab from data points in a set location. that way every week if i run the macro it will grab from the newer updated data. because of the shift everything gets really messed up with these graphs so i really just want to overwrite cell values.
 
Upvote 0
I got the code working but it isn't what I'm looking for. the Original Problem was that it was missing an "End With" which was foolish of me to not have seen.

When it inputs from the text file it throws everything into Column A. The text file has tabs in the proper places and I need it to output into the proper columns. if i have "word" (tab)"other" in the text file i need excel to have "word" in column a and "other" in column b.

Another problem with this code is that it doesn't overwrite like I wanted it too. I puts everything in column a, and then shfits all previous excel material over 1 column so it all starts at b. I stated why this is a problem already.
 
Upvote 0
I got the code working but it isn't what I'm looking for. the Original Problem was that it was missing an "End With" which was foolish of me to not have seen.

When it inputs from the text file it throws everything into Column A. The text file has tabs in the proper places and I need it to output into the proper columns. if i have "word" (tab)"other" in the text file i need excel to have "word" in column a and "other" in column b.

Another problem with this code is that it doesn't overwrite like I wanted it too. I puts everything in column a, and then shfits all previous excel material over 1 column so it all starts at b. I stated why this is a problem already.

Revised Code:

Code:
Sub Import_test()
'
' Import_test
'
'
    Application.CutCopyMode = False
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\xxx\name_of_file.txt", Destination:=Range("$A$1"))
        .Name = "name_of_file"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Make the changes to suit your setup as I've outlined previously.
 
Upvote 0

Forum statistics

Threads
1,224,592
Messages
6,179,777
Members
452,942
Latest member
VijayNewtoExcel

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