I would like to append data from Columns A-F to a text file. The key thing is that the data needs to be tab separated just as it would be if I simply highlighted it and copied it and the pasted it into the text file.
If you need any more information, please let me know and I'll be happy to supply it.
Your help is appreciated
Re: Append To Text File
Try somewhat like this :
For iCount = 1 To Range("RowCounter")
Open sDir & "\Facturen.dat" For Append As #1
Write #1, _
Right(ActiveSheet.Cells(iCount, 1), 6); ActiveSheet.Cells(iCount, 2); A; ActiveSheet.Cells(iCount, 4); _
ActiveSheet.Cells(iCount, 5); Right(ActiveSheet.Cells(iCount, 6), 5); ActiveSheet.Cells(iCount, 7); _
ActiveSheet.Cells(iCount, 8); Left(ActiveSheet.Cells(iCount, 9), 3); Right(ActiveSheet.Cells(iCount, 10), 4); _
ActiveSheet.Cells(iCount, 11); ActiveSheet.Cells(iCount, 12)
Close #1
Next iCountRe: Append To Text File
This puts the data using comma separated values. I really need it to be Tab separated.
Re: Append To Text File
As WinteE suggested you need to build the information within a loop.
[vba]Sub x()
Dim lngRow As Long
Dim lngCol As Long
Dim strBuf As String
Dim intUnit As Integer
intUnit = FreeFile
Open "filename.txt" For Append As #intUnit
For lngRow = 1 To 10
strBuf = ""
For lngCol = 1 To 6
strBuf = strBuf & Cells(lngRow, lngCol) & vbTab
Next
Print #intUnit, Left(strBuf, Len(strBuf) - 1)
Next
Close intUnit
End Sub
[/vba]
Re: Append To Text File
Thanks for this. I have one more question. How can I save the files as a particular name. I would like to save the files using a date field so for example, "item move file 190407.txt". Here is my code that I have so far:
Dim vRow 'Used for working row
Dim Building 'Used to identify building from Post Code
Dim PostCode 'Strips off last 7 characters of address to generate Post Code
Dim uName 'User name of person running the import
Dim x
Dim lngCol As Long
Dim strBuf As String
Dim intUnit As Integer
Private Sub move_data()
'================================================================================
'This macro is designed to get the information from CC and import it to Assyst
'Step 1 - It asks who is running the macro. This is useful to record in Assyst
'Step 2 - Clears all data fromm Row 2 to Row 1000 in the Move and Change Sheets
'Step 3 - Strips off last 7 characters of address to generate Post Code
'Step 4 - Runs Vlookup to identify the Building from the Post Code
'Step 5 - Copies the data
'================================================================================
uName = InputBox("Please enter your name")
For Each x In Array(2, 3, 4, 5)
Sheets(x).Activate
Range(Cells(2, 1), Cells(1000, 255)).Delete
Next x
For vRow = 2 To Sheet1.UsedRange.Rows.Count
PostCode = Trim(Right(Sheet1.Cells(vRow, 6), 7))
Building = WorksheetFunction.VLookup(PostCode, Sheet6.Range("A2:B1000"), 2, False) & "-"
'============= Data Copy Begins ================================================
'Copies Pc Number, Adds Ref nos, Building, Employee No and Person making changes
'===============================================================================
Sheet2.Cells(vRow, 1).NumberFormat = "@"
Sheet2.Cells(vRow, 1).Value = Sheet1.Cells(vRow, 7).Value
Sheet2.Cells(vRow, 2).Value = "CC Ref " & Sheet1.Cells(vRow, 3).Value & " & EA Ref " & Sheet1.Cells(vRow, 4).Value & " " & Sheet1.Cells(vRow, 10).Value
Sheet2.Cells(vRow, 3).Value = Building
Sheet2.Cells(vRow, 4).Value = Sheet1.Cells(vRow, 2).Value
Sheet2.Cells(vRow, 6).Value = uName
'============= Data Copy for Item Move Ends ====================================
'
'Now to fill in the Item Change Sheet
'
'============= Data Copy Begins ================================================
'Copies Pc number, Building, Employee No (3 times) and person running macro
'===============================================================================
Sheet3.Activate
Sheet3.Cells(vRow, 1).NumberFormat = "@"
Sheet3.Cells(vRow, 1).Value = Sheet1.Cells(vRow, 7).Value
Sheet3.Cells(vRow, 2).Value = Building
Sheet3.Cells(vRow, 3).Value = Sheet1.Cells(vRow, 2).Value
Sheet3.Cells(vRow, 4).Value = Sheet1.Cells(vRow, 2).Value
Sheet3.Cells(vRow, 5).Value = Sheet1.Cells(vRow, 2).Value
Sheet3.Cells(vRow, 6).Value = uName
'============= Data Copy for Disposal Begin ====================================
'Copies Pc Number, Adds Ref nos, Building, Employee No and Person making changes
'===============================================================================
Sheet4.Activate
Sheet4.Cells(vRow, 1).NumberFormat = "@"
Sheet4.Cells(vRow, 1).Value = "0" & Sheet1.Cells(vRow, 9).Value
Sheet4.Cells(vRow, 2).Value = "Disposed of from " & "CC Ref " & Sheet1.Cells(vRow, 3).Value & " & EA Ref " & Sheet1.Cells(vRow, 4).Value & " " & Sheet1.Cells(vRow, 10).Value
Sheet4.Cells(vRow, 3).Value = Building
Sheet4.Cells(vRow, 4).Value = Sheet1.Cells(vRow, 2).Value
Sheet4.Cells(vRow, 6).Value = uName
'============= Data Copy for Item Move Ends ====================================
'
'Now to fill in the Item Change Sheet
'
'============= Data Copy Begins ================================================
'Copies Pc number, Building, Employee No (3 times) and person running macro
'===============================================================================
Sheet5.Activate
Sheet5.Cells(vRow, 1).NumberFormat = "@"
Sheet5.Cells(vRow, 1).Value = "0" & Sheet1.Cells(vRow, 9).Value
Sheet5.Cells(vRow, 2).Value = Building
Sheet5.Cells(vRow, 3).Value = Sheet1.Cells(vRow, 2).Value
Sheet5.Cells(vRow, 4).Value = Sheet1.Cells(vRow, 2).Value
Sheet5.Cells(vRow, 5).Value = Sheet1.Cells(vRow, 2).Value
Sheet5.Cells(vRow, 6).Value = uName
Sheet5.Activate
Next vRow
Sheet2.Activate
intUnit = FreeFile
Open "c:\testing\item move header file.txt" For Append As #intUnit
For vRow = 2 To Sheet1.UsedRange.Rows.Count
strBuf = ""
For lngCol = 1 To 6
strBuf = strBuf & Cells(vRow, lngCol) & vbTab
Next
Print #intUnit, Left(strBuf, Len(strBuf) - 1)
Next
Close intUnit
Sheet3.Activate
intUnit = FreeFile
Open "c:\testing\item change header.txt" For Append As #intUnit
For vRow = 2 To Sheet1.UsedRange.Rows.Count
strBuf = ""
For lngCol = 1 To 6
strBuf = strBuf & Cells(vRow, lngCol) & vbTab
Next
Print #intUnit, Left(strBuf, Len(strBuf) - 1)
Next
Close intUnit
End SubRe: Append To Text File
Please remember to use code tags.
[vba]Open "item move file "&format(now(),"ddmmyy") & ".txt" For Append As #intUnit[/vba]
Don’t have an account yet? Register yourself now and be a part of our community!