12/23/2015

ExcelVBAReadAndWriteTextFiles~MyEngineeringWorld

Excel VBA Read And Write Text Files

WrittenbyChristosSamarasonSunday,17June2012at16:33

Enteryouremailaddress...

Submit

Help us make this site even better!

Figure1:Exceldata.

Figure2:Textdata.

If you need an answer...

Theintegrationbetweenexcelandtextfilesisstillanimportantissue.Theinputandoutputofmanydatedprogramsis
usually a text file (for example various calculation algorithms written in FORTRAN language). So, if you have a
spreadsheetandyouneedsomehowtowritedatatoatextfileorread/importdatafromatextfilethefollowingVBAcode
willhelpyouautomatingtheprocedure.

Howtodoit

http://www.myengineeringworld.net/2012/06/excelvbareadandwritetextfiles.html

1/16

12/23/2015

ExcelVBAReadAndWriteTextFiles~MyEngineeringWorld

Write:Therearetwowaysofwritingdatatoatextfile:youcaneitheruseWriteorPrintstatement.TheoutputofWrite
statement is a line in which the data values are separated by commas, while hash marks (#) are around dates and
quotes()aroundstrings.TheoutputofPrintstatementisalinethatismoresuitableforprinting,withdatavaluesbeing
arrangedincolumnsseparatedwithtaboranyotherdelimitedcharacter.

Read:Aswiththewriteproceduretherearealsotwowaysofreadingdatafromatextfile:youcaneitheruseInputor
LineInputstatement.WiththeInputstatementdatavaluesarereadaslistofvariables.Inputstatementisusedfordata
thatwereproducedwithWritestatement.Ontheotherhand,withtheLineInputstatementallthedataarereadasa
singlestring,soLineInputissuitableforreadingdatavaluesproducedbyPrintstatement.

VBAcode

Professional Excel development

VBAcodeforwritingtotextfileusingWritestatement:

Sub WriteTextFile()

'Write data to a text file

'By Christos Samaras

'Declaring variables
Dim sDrawing As String
Dim sWeld As String
Dim sWelder As String
Dim intSize As Integer
Dim dDate As Date
Dim sMaterial As String

Dim sFName As String

Dim intFNumber As Integer
Dim lCounter As Long
Dim lLastRow As Long

Top 3 Youtube videos

Create&OpenKMLKMZFilesWithGoogleMaps&
GoogleEarth
AutoCADVBACalculatePolylinesLength&Area
DrawAPolylineInAutoCADUsingExcelVBA

Top 5 stories

'Just showing where the input data are

Sheet2.Activate

ExcelVBAReadAndWriteTextFiles

Range("A1").Select

Figure1:Exceldata.Figure2:Textdata.

Theintegrationbetweenexcelandtext

'Find the last row that contains data

filesisstillanimportantissue.Theinp...

With Sheet2
lLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

VBAMacroToOpenAPDFFile

End With

Thepreviousweek,whileIwas

preparingapresentation,Iencountered

'Setting the name and the path of text file based on workbook path

thefollowingproblem:howcouldIopen

sFName = ThisWorkbook.Path & "\Excel Data (Write).txt"

aPDFfilefromapower...

VBAMacroToConvertPDFFilesInto

'Get an unused file number

DifferentFormat

intFNumber = FreeFile

Acoupleofdaysago,Peteraskedme

'Create a new file (or overwrite an existing one)

onthispostifitispossibletosaveaPDF
fileasanExceltablebyusingVBA.I

Open sFName For Output As #intFNumber

For lCounter = 2 To lLastRow

'Read specific data from the worksheet
With Sheet2
sDrawing = .Cells(lCounter, 2)
sWeld = .Cells(lCounter, 3)
sWelder = .Cells(lCounter, 4)

startedthink...
NumericalIntegrationInExcelUsing
TheTrapezoidalRule
ThebasicsAcommoncomplainabout
Excelisthatitdoesntprovideadirect
methodtocalculatetheintegralofa
function.Ifthe...

intSize = .Cells(lCounter, 5)

DrawAPolylineInAutoCADUsing

dDate = .Cells(lCounter, 7)

ExcelVBA

sMaterial = .Cells(lCounter, 10)

End With

http://www.myengineeringworld.net/2012/06/excelvbareadandwritetextfiles.html

Afriendofmine,whoissurveying
engineer,askedmerecentlyifitis

2/16

12/23/2015

ExcelVBAReadAndWriteTextFiles~MyEngineeringWorld

possibletodrawapolylineinAutoCADusing

'Write selected data to text file

coordinatesfro...

Write #intFNumber, sDrawing, sWeld, sWelder, intSize, dDate, sMaterial

'Continue looping until the last row

Next lCounter

'Close the text file

Close #intFNumber

'Inform the user about the process

MsgBox "Values from sheet '" & Sheet2.Name & "' were written to '" & sFName & "' file
!", vbInformation

Hidden treasures ...from 2011

HowToDevelopASmallHydropowerSite
10+1WaysToFindTheFrictionFactorInPipes
DependentListsInExcel
TheRoleOfCapacityFactor
IsometricCAD

End Sub

Searchthissite...

VBAcodeforreadingatextfileusingInputstatement:

Sub ReadTextFile()
'Read data from a text file
'By Christos Samaras
'Declaring variables
Dim sDrawing As String
Dim sWeld As String
Dim sWelder As String
Dim intSize As Integer
Dim dDate As Date
Dim sMaterial As String
Dim sFName As String
Dim intFNumber As Integer
Dim lRow As Long
'The full path of the text file that will be opened
sFName = ThisWorkbook.Path & "\Excel Data (Write).txt"
'Get an unused file number
intFNumber = FreeFile
On Error Resume Next
'Prepare text file for reading
Open sFName For Input As #intFNumber
'Check if the text file was found
If Err.Number <> 0 Then
MsgBox "Text file not found!", vbCritical, "Error!"
Exit Sub
End If
On Error GoTo 0

'Clearing the sheet

Sheet3.Cells.Clear
lRow = 1
'Loop until the end of file
Do While Not EOF(intFNumber)

http://www.myengineeringworld.net/2012/06/excelvbareadandwritetextfiles.html

3/16

12/23/2015

ExcelVBAReadAndWriteTextFiles~MyEngineeringWorld
'Read data from the text file
Input #intFNumber, sDrawing, sWeld, sWelder, intSize, dDate, sMaterial
'Write selected data to the worksheet
With Sheet3
.Cells(lRow, 1) = sDrawing
.Cells(lRow, 2) = sWeld
.Cells(lRow, 3) = sWelder
.Cells(lRow, 4) = intSize
.Cells(lRow, 5) = dDate
.Cells(lRow, 6) = sMaterial
End With
'Address next row of worksheet
lRow = lRow + 1

Loop
'Close the text file
Close #intFNumber
'Fitting column width
Sheet3.Cells.EntireColumn.AutoFit
'Just showing where the output data are
Sheet3.Activate
Range("A1").Select
'Inform the user about the process
MsgBox "Values from file '" & sFName & "' were imported to sheet '" & Sheet3.Name & "
'!", vbInformation
End Sub

VBAcodeforwritingtotextfileusingPrintstatement:

Sub PrintAsString()

'With print command you can write text files in any format
'Here tab is used as delimited character
'By Christos Samaras

'Declaring variables
Dim sLine As String
Dim sFName As String
Dim intFNumber As Integer
Dim lCounter As Long
Dim lLastRow As Long

'Just showing where the input data are

Sheet2.Activate
Range("A1").Select

'Find the last row that contains data

With Sheet2
lLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

'Setting the name and the path of text file based on workbook path
sFName = ThisWorkbook.Path & "\Excel Data (Print).txt"

http://www.myengineeringworld.net/2012/06/excelvbareadandwritetextfiles.html

4/16

12/23/2015

ExcelVBAReadAndWriteTextFiles~MyEngineeringWorld

'Get an unused file number

intFNumber = FreeFile

'Create a new file (or overwrite an existing one)

Open sFName For Output As #intFNumber

For lCounter = 2 To lLastRow

'Read specific data from the worksheet
With Sheet2
'Using tab as delimited character
sLine = .Cells(lCounter, 2) & vbTab
sLine = sLine & .Cells(lCounter, 3) & vbTab
sLine = sLine & .Cells(lCounter, 4) & vbTab
sLine = sLine & .Cells(lCounter, 5) & vbTab
'Determine the date format
sLine = sLine & Format(.Cells(lCounter, 7), "dd-mm-yyyy") & vbTab
sLine = sLine & .Cells(lCounter, 10)
End With

'Write data to file

Print #intFNumber, sLine

'Continue looping until the last row

Next lCounter

'Close the file

Close #intFNumber

'Inform the user about the process

MsgBox "Values from sheet '" & Sheet2.Name & "' were written to '" & sFName & "' file
!", vbInformation

End Sub

VBAcodeforreadingatextfileusingLineInputstatement:

Sub ReadStringData()

'To read "string" data, the string must be split in order to locate the values
'By Christos Samaras

'Declaring variables
Dim sLine As String
Dim sFName As String
Dim intFNumber As Integer
Dim lRow As Long
Dim lColumn As Long
Dim vDataValues As Variant
Dim intCount As Integer

'The full path of the text file that will be opened

sFName = ThisWorkbook.Path & "\Excel Data (Print).txt"

'Get an unused file number

intFNumber = FreeFile

On Error Resume Next

'Prepare text file for reading

Open sFName For Input As #intFNumber

http://www.myengineeringworld.net/2012/06/excelvbareadandwritetextfiles.html

5/16

12/23/2015

ExcelVBAReadAndWriteTextFiles~MyEngineeringWorld

'Check if the text file was found

If Err.Number <> 0 Then
MsgBox "Text file not found!", vbCritical, "Error!"
Exit Sub
End If

On Error GoTo 0

'Clearing the sheet

Sheet3.Cells.Clear

'First row for data

lRow = 1

'Loop until the end of file

Do While Not EOF(intFNumber)

'Read data from file

Line Input #intFNumber, sLine

'Split values apart into an array

vDataValues = Split(sLine, vbTab)

With Sheet3

'First column for data

lColumn = 1

'Process each value in the array

For intCount = LBound(vDataValues) To UBound(vDataValues)

'Write the value to the worksheet

.Cells(lRow, lColumn) = vDataValues(intCount)

'Increase column count

lColumn = lColumn + 1
Next intCount

End With

'Next row of the worksheet

lRow = lRow + 1

Loop

'Close the file

Close #intFNumber

'Fitting column width

Sheet3.Cells.EntireColumn.AutoFit
'Just showing where the output data are
Sheet3.Activate
Range("A1").Select
BlogContents
Home

ExcelDevelopment

AdvertiseHere

ERTATAddIn

SuggestedBooks

About

Disclaimer

F.A.Q.

'Inform the user about the process

MsgBox "Values from file '" & sFName & "' were imported to sheet '" & Sheet3.Name & "
'!", vbInformation
End Sub

Video

http://www.myengineeringworld.net/2012/06/excelvbareadandwritetextfiles.html

6/16

12/23/2015

ExcelVBAReadAndWriteTextFiles~MyEngineeringWorld

Thisshortvideodemonstratesthefoursubsinaction.

15
Like

Excel VBA -- Read And Write Text Files

Tweet

17

Downloaditfromhere

ThissamplefilecontainstheVBAcodedescribedabove.Inthefirstsheettherearefourbuttonsthatcorrespondtothe
fourprocedures(Write,Input,Print,LineInput).Inthesecondsheettherearesomedatathatareexportedtoatextfile.
Finally,thethirdsheetisusedtoimportdatafromatextfile.ThefilecanbeopenedwithOffice2007ornewer.Please,
remembertoenablemacrosbeforeusingtheworkbook.

Readalso

ImportingSpecificColumnsFromATextFile
Import&ExportCSVFiles

Didyoulikethispost?Ifyes,thenshareitwithyourfriends.Thankyou!

Like

15

Tweet

StumbleUpon

17

Categories:OfficeTips

ChristosSamaras

http://www.myengineeringworld.net/2012/06/excelvbareadandwritetextfiles.html

7/16

12/23/2015

ExcelVBAReadAndWriteTextFiles~MyEngineeringWorld

MechanicalEngineer(Ph.D.cand.),M.Sc.CranfieldUniversity,Dipl.Ing.AristotleUniversity,ThessalonikiGreece.
Communication:tel.+306973513308,email,Facebook,Twitter,Google+andLinkedin.FullCVhere.

NewerPost

Home

OlderPost

Commentsforthisthreadarenowclosed.

54Comments

MyEngineeringWorld

Recommend

Share

Login

SortbyNewest

Abe 2yearsago

Thankyouverymuchfortheabove.Iwouldliketouseyourprintmethodforexporting
totxt.However,thecellsIwanttoexportcontaintextandhavelinebreaks.WhenI
exportthem(withvbNewLineinsteadofvbTab,becauseIwanttohavethecellsbelow
eachother),thelinebreaksfromthecellsareignored.Istheresomewaytokeepthem,
sothatonecellcanresultinmultiplelinesintxtformat?

Share

Abe>Abe 2yearsago

Ifoundtheproblem:).IhadusedChr(10)forthelinebreaksinthetextand
notepaddoesn'trecognizethose...IreplacedthatbyvbNewLineanditlooks
goodnow.

Share

ChristosSamaras

Admin >Abe

2yearsago

HiAbe,
SorryformylatereplaybutIwasonshortvacations,sotodayIsaw
yourpost.Iamgladthatyousolveyourproblem.
BestRegards,
Christos

Share

hhap 2yearsago

Thanksfortheabovetutorial.Pleasehowdoyououtputorprintaline/dash(e.g
)inatextfileusingexcelvba.

Share

ChristosSamaras

Admin >hhap

2yearsago

Hihhap,
Inthewritemacroforexampleyoucanusethisline:
'Writeselecteddatatotextfile
Write#intFNumber,sDrawing,"",sWeld,"",sWelder,intSize,dDate,sMaterial

Thelinedashisusedasstring.
KindRegards,
Christos

Share

hhap>ChristosSamaras 2yearsago

Christos,
Thankyoufortheeducation.Ididn'tknowthisone.Iwasgoingtouse
thisfunction:Application.WorksheetFunction.Rept("",65)

Share

ChristosSamaras

Admin >hhap

2yearsago

Ingeneral,theReptfunctionisbettertobeusedwhenyouknow
http://www.myengineeringworld.net/2012/06/excelvbareadandwritetextfiles.html

8/16

12/23/2015

ExcelVBAReadAndWriteTextFiles~MyEngineeringWorld

Ingeneral,theReptfunctionisbettertobeusedwhenyouknow
exactlyhowmanydashlinesforexampleyouwillneed.Ifinyour
caseyouneedtocreate65dashlinesthenusetheReptfunction
likethis:
'Writeselecteddatatotextfile

Write#intFNumber,sDrawing,Application.WorksheetFunction.Rept("",65),sWeld,Application.WorksheetFunction.Rept("

Ifyouneedonlyfewdashlinesthenusethepreviouslineofcode.

Share

hhap>ChristosSamaras 2yearsago

Itriedusingwhatyoushowedmeandexcelsaysvariablenot
defined.JusttobeclearIneedtodefinethefollowingasString?
#intFNumber,sDrawing,"",sWeld,"",sWelder,
intSize,dDate,sMaterial

Share

ChristosSamaras

Admin >hhap

2yearsago

Thetwolinesinthecomments(fordashlines)werereferringto
the"WriteTextFile"macro.So,Ijustshowedyouhowyoucan
insertthedashlinesintheoriginalcode.
Inthe"WriteTextFileabove"therearethesevariables:
DimsDrawingAsString
DimsWeldAsString
DimsWelderAsString
DimintSizeAsInteger
DimdDateAsDate
DimsMaterialAsString

whichare"written"tothetextfile.
Inyourcaseyoumighthavedifferentvariables,soyouneedto
makechangestotheabovecode.
Tosumup,thecodeaboveisasampleforaspecificcase.
However,youcanadjustitaccordingtowhatyouwanttodo...

Share

J 2yearsago

Thankyou!Thisisveryhelpful.Thanksforprovidingthetextfilestodownloadgoing
throughtheexampledefinitelyhelped.IamabitconfusedastowhyIcannotreadthe
textfileintoExcelthough.IprettymuchcopiedyourReadStringDatacode(savedyour
file,too),butIkeepongettingtheerrorbox(textfilenotfound).Isitsomethingwrong
withthisline:sFName=ThisWorkbook.Path&"\ExcelData(Write).txt"sinceit'ssaved
differentlyonmycomputer?

Share

ChristosSamaras

Admin >J

2yearsago

HiJandthankyourforyourkindcommentregardingthecode.
Thisline:
sFName=ThisWorkbook.Path&"\ExcelData(Write).txt"

impliesthatthetextfileshouldbeatthesamefolderwithexcelfile.So,ifthe
excelfileisinthefolderC:\Users\Christos\Desktop,thenthesFNamestringwill
be:C:\Users\Christos\Desktop\ExcelData(Write).txt
Apartformthepath,ifyouhaverenamedthetextfileforexamplefrom"Excel
Data(Write)"to"WriteTextFile"thenchangethesFNametothis:
sFName=ThisWorkbook.Path&"\WriteTextFile.txt"

Ihopethattheabovesuggestionwillhelpyoutosolvetheproblem.
http://www.myengineeringworld.net/2012/06/excelvbareadandwritetextfiles.html

9/16

12/23/2015

ExcelVBAReadAndWriteTextFiles~MyEngineeringWorld

KindRegards,
Christos

Share

m_andrade 2yearsago

Thankyousomuch!!!
Itworkedperfectly!!

Share

ChristosSamaras

Admin >m_andrade

2yearsago

You'rewelcome!
Iamgladthatitworkedforyou.)

Share

shriK 3yearsago

Christosdude..yourprimarylinkisdown.
Pleasefixit.
Thanksinadvance.

Share

ChristosSamaras

Admin >shriK

3yearsago

Whichlinkisdown?
Icheckedtheworkbookdownloadlinkandisfine.
Ifsomethingiswrongpleasetellmeinordertofixit.
Thankyouinadvanced!
Christos

Share

ChristosSamaras 3yearsago

HiSamT,
Thankyouverymuchforthefunctions.
Itisalwaysnicetoseehowan"old"codecanbeusedtocreatesomethingnew.Thank
youalsoforthereferenceinyourfunctions.
Keepcoding...
KindRegards,
Christos

Share

Anonymous 3yearsago

FunctionWriteLinesToTextFile(FileFullNameAsString,LinesToWriteAsCollection)As
Boolean
'BySamT:"samtyler"&X&"mail"&Y&"com"_
whereX="@"andY="."
'WiththankstoChristosSamaras:http://www.myengineeringworld....
'FileFullNamemustincludethefullPathandnameoftheFile.
'Ifthefileexists,itwillbeoverwritten.
'IftheFunctionsucceeds,itwillreturn"True"_
ElseIfitfailsforanyreason,itwillreturn"False.
''''SetErrorCondition
OnErrorGoToReportError
''''DeclareVariables
http://www.myengineeringworld.net/2012/06/excelvbareadandwritetextfiles.html

10/16

12/23/2015

ExcelVBAReadAndWriteTextFiles~MyEngineeringWorld
seemore

Share

Anonymous 3yearsago

SamT,again.
ItutorVBAononeofthemanyVBAforums,andwehaveaguestwhoneedstoperform
acomplicatedparseandreplaceorinsertlines.
Irewrotetwoofyourproceduresasuniversalfunctionsusingcollectionstopassthe
lineStringsbackandforth.
Ithoughtyoumightbeinterestedinseeingadifferentwayofdoingthings.
Ihavetobreakthefunctionsacrosstwocomments.
FunctionLinesFromTextFile(FileFullNameAsString)AsCollection
'BySamT:"samtyler"&X&"mail"&Y&"com"_
whereX="@"andY="."
'WiththankstoChristosSamaras:http://www.myengineeringworld....
'FileFullNamemustincludethefullPathandnameoftheFile.
seemore

Share

ChristosSamaras 3yearsago

ThankyouverymuchSamT!
Iamjusttryingtoincludeasmuchcommentsaspossibleinordertobeeasily
understood...
WarmRegards,
Christos

Share

Anonymous 3yearsago

IloveyourcodingstyleVeryeasytounderstand.
Thankyou,
SamT

Share

ChristosSamaras 3yearsago

HiJames,
Sinceyourproblemwassolved,noproblematall.
KindRegards,
Christos

Share

James 3yearsago

ThanksChristos,thatworksnicely.IthinkImisreadyourearlierstatement,whichis
whyIwasn'tsurewhichonetouse.
Thanksagain!
James

Share

ChristosSamaras 3yearsago

HiJames,
http://www.myengineeringworld.net/2012/06/excelvbareadandwritetextfiles.html

11/16

12/23/2015

ExcelVBAReadAndWriteTextFiles~MyEngineeringWorld

YoushouldusethePrintmacro.
AsIwroteinthebeginningofthispost,TheoutputofWritestatementisalineinwhich
thedatavaluesareseparatedbycommas,whilehashmarks(#)arearounddatesand
quotes()aroundstrings.
So,sincethequotesareautomaticallyinsertedduetoWritestatement,itwouldbe
easiertousethePrintstatement.
WarmRegards,
Christos

Share

James 3yearsago

HiChristos,
I'musingthewriteTextFilecodebutit'sincludingthequotationmarksforeachcell.DoI
havetousethePrintasstringcodeinsteadtoexcludethequotationsoristhereanother
modificationIcanuse?Itworksreallywellapartfromthis.
Thanks,
James

Share

ChristosSamaras 3yearsago

HiRudi,
Iamgladthatyoufoundasolution.IjustchangedonelinebecauseIthoughtthatyou
haveonlydates.
Anyway,wheneveryouwantsendmeyoursolutions.
BestRegards,
Christos

Share

FranseFietsersClub 3yearsago

HiChristos,
Thanksforyourquickresponse.Yoursolutionsworks!Justhadtoexcludetheother
numericalfieldswithanifconstructionbecausetheywerealsochangedtodateformats.
InthemeantimeIalsofoundanotherworkaround.Iwillsendyouanemailwithboth
solutions,youmightwanttospentablogonitonwhichoneisfavourable,better,faster,
....
Thanksalotankindregards,Rudi

Share

ChristosSamaras 3yearsago

HiRudi,
Hereisaquickidea:
Changethisline
oTSStream.WriteCStr(rngeRangeToWrite.Cells(lngRow,lngCol))&strDelimiter
withthis:
oTSStream.WriteFormat(CStr(rngeRangeToWrite.Cells(lngRow,lngCol)),
"dd/mm/yyyy")&strDelimiter
Ihaven'ttestit,buttheideaistoformatthedateinadd/mm/yyyyformat.
http://www.myengineeringworld.net/2012/06/excelvbareadandwritetextfiles.html

12/16

12/23/2015

ExcelVBAReadAndWriteTextFiles~MyEngineeringWorld

Ihaven'ttestit,buttheideaistoformatthedateinadd/mm/yyyyformat.
Ifyouarestillhavingtroublessendmeanemailwiththesamplefile.
Regards,
Christos

Share

FranseFietsersClub 3yearsago

HiChristos,imstrugglingwithanexportofadateformat.
Whenenteringthe7thofmarchinmyspreadsheetandusingvbacodetoincludethis
dateinancsvfile,thedateisalwaystruncatedto7/03/2013,andireallyneeditas
07/03/2013,sowiththeleading0.inmyspreadsheetitiscorrectlyvisualised,butupon
exportusing:
'Thisloopisforloopingfromthetopofthesheettothebottom
ForlngRow=1TorngeRangeToWrite.Rows.Count
Application.StatusBar="Record"&lngRow&"of"&myNrOfRec&"isbeing
exported"
'Thisloopwillwriteonecellatatimeforthecurrentrow
ForlngCol=1TorngeRangeToWrite.Columns.Count
oTSStream.WriteCStr(rngeRangeToWrite.Cells(lngRow,lngCol))&strDelimiter
NextlngCol
seemore

Share

ChristosSamaras 3yearsago

HiRobby,
IfyouwanttoimportonlyrowsthatcontainW4thenyoushouldusethecodethat
follows.Ifyouwanttoimportspecificcolumnsfromthetextfileyoushouldtrythistool
thatIdevelopedsomemonthsago.
Regards,
Christos
SubReadTextFile2()
'Readdatafromatextfile
'ByChristosSamaras
'Declaringvariables
DimsDrawingAsString
DimsWeldAsString
DimsWelderAsString
seemore

Share

RobbySuhendra 3yearsago

DearSir,
Yourcodeisveryusefulinmyofficeproject.ifyoudon'tmind,iwouldliketoknowhow
toimportdatafromtextfileintoexcelbutonlyspecificdata/filterdatathatmeetthe
specificcriteriatobeimported.asexamplefromyourtextfile,letsayweonlywantto
importthe"w4"datafromthetextfile.
couldyoupleasetellmehowtodothat?
Thanks
http://www.myengineeringworld.net/2012/06/excelvbareadandwritetextfiles.html

13/16

12/23/2015

ExcelVBAReadAndWriteTextFiles~MyEngineeringWorld

Regards
Robby

Share

ChristosSamaras 3yearsago

fetsch,Isawyouremail...IwillsendyouareplyassoonasIfindasolution...

Share

mfetsch 3yearsago

Christos,Isentyouanemailearlier.Letmeknowifyoudon'tgetitforsomereason.

Share

ChristosSamaras 3yearsago

mfetsch,myemailisxristos.samaras@gmail.com

Share

mfetsch 3yearsago

Ireallyappreciatethat,Christos.Whatisyouremailaddress?

Share

ChristosSamaras 3yearsago

mfetsch,
Ifyoulikesendmeasamplefile(excelandtextfile)tomyemailandIwilltrytosolve
yourproblem...

Share

mfetsch 3yearsago

Iappreciatethat,Christos,butthexlTextfileformatsavesatabdelimitedfile.Idon't
wanttabsoranyotherdelimiterbetweenthecolumns.WhenIusedthexlTextPrinterfile
format,itgavemewhatIwant,butit'snotworkingsincetheOfficeupdate.

Share

ChristosSamaras 3yearsago

mfetsch,IwrotethefollowingVBAcodeonthefly,butIthinkthatwilldowhatyou
need...

OptionExplicit
SubSheetsToTexts()
DimshtAsWorksheet
WithApplication
.ScreenUpdating=False
.DisplayAlerts=False
EndWith
ForEachshtInThisWorkbook.Worksheets
Sheets(sht.Name).Select
Sheets(sht.Name).Copy
seemore

Share

mfetsch 3yearsago

Thanks,Christos.Iwashopingtoprinteachrowwithoutconcatenatingallthecolumns
first.Actually,I'vebeenhopingtofindawaytosaveawholesheettoatextfile.Iwas
doingthat,butthecodeisnolongerworkingafteranOfficeupdate.Here'sthecode:
ActiveSheet.SaveAsFilename:="C:\SAMHfiles\ASAMinput.txt",
FileFormat:=xlTextPrinter,CreateBackup:=False
ItseemsthexlTextPrinterfileformatishavingissues.Columnsaremissingdataor
http://www.myengineeringworld.net/2012/06/excelvbareadandwritetextfiles.html

14/16

12/23/2015

ExcelVBAReadAndWriteTextFiles~MyEngineeringWorld
ItseemsthexlTextPrinterfileformatishavingissues.Columnsaremissingdataor
startinginthewrongplace.Ihaven'tbeenabletofindanotherformatthatwillputthe
dataoutwithoutanydelimiters.SoIdecidedtocheckintootherwaysofwritingatext
file.Thanksforyourhelp!

Share

ChristosSamaras 3yearsago

IfyouwanttoprinttoatextfileALLcolumnsofasheet,thenusethecodebelow(which
isamodifiedversionofprintstatement):
OptionExplicit
SubPrintAllColumns()
'Withprintcommandyoucanwritetextfilesinanyformat
'Heretabisusedasdelimitedcharacter
'ByChristosSamaras
'Declaringvariables
DimsLineAsString
DimsFNameAsString
DimintFNumberAsInteger
DimlCounterAsLong
DimlLastRowAsLong
DimlLastColumnAsLong
seemore

Share

mfetsch 3yearsago

IwanttoprintmyExcelsheettoatextfile.IsthereawaytoPrint
awholerowofExceldataatonceratherthanconcatenating
allthecolumnstogether?Ihaveanawfullotofcolumns!Thanks!

Share

ChristosSamaras 3yearsago

Ijustsentyouthenewversionoftheworkbook...Problemsolved!

Share

robertshanePascual 3yearsago

Gooddaysir,sorryIwasn'tabletoreplytoyouasap,butiemailedyoualready.please
seeyourmailbox.Thankyousomuchforthehelp!andimsorrytodisturbyoualso
hehehe:).pleasereply.thankyouagain.

Share

ChristosSamaras 3yearsago

Robert,Ijustsentyouanemail.Don'tworry,Icheckmyemailseveryday...

Share

robertshanePascual 3yearsago

GoodDayagainsir,
Pleaseseemyemail.Ihavesentthedetailsandtheattachedfiles.ThankYou!:)

Share

ChristosSamaras 3yearsago

GoodmorningRobertandthankyoufrothekindcomment.
Ihavealreadysentyouanemail.)

Share

robertshanePascual 3yearsago

Gooddaysir,
Thankyouforthefile,itworksgreat!:)butineedsomecustomizationingeneratingtext
file.Ihopeyoucanhelpme.
Thanksinadvance!:)
http://www.myengineeringworld.net/2012/06/excelvbareadandwritetextfiles.html

15/16

12/23/2015

ExcelVBAReadAndWriteTextFiles~MyEngineeringWorld

Thanksinadvance!:)

Share

ChristosSamaras 3yearsago

Ijustsendyouareply.Don'tworry,Icheckmyemailseveryday..)

Share

gert 3yearsago

heymenisendyouamail

Share

ChristosSamaras 3yearsago

Noproblematall!
IfyoucannotchangethecodeIwilldoitforyou...)

Share

gert 3yearsago

iwilltrythisandiwillbotheryouwithamailifi'mhavingtroublehahahaThanksmen

Share

Loadmorecomments

Subscribe

AddDisqustoyoursiteAddDisqusAdd

Privacy

Copyright20112015:ChristosSamaras

http://www.myengineeringworld.net/2012/06/excelvbareadandwritetextfiles.html

FollowusonFacebook,Twitter,Google+,Linkedin,Youtube&Feedburner

16/16