Upon clicking a button, columns A -D will be copied to notepad. In the notepad, there should be additional items added on top of the values copied from Excel.
Sample template of both excel and notepad as attached. Would greatly appreciate if anyone could set up the VBA codes or at least guide me on how I should start. Thanks.
Sample1.xlsxSample1.txt
Sub Data2Text() Dim fs, obj As Object Dim Folder, FileName, Text As String Dim lR, i, c As Long Folder = ThisWorkbook.Path & "\" FileName = " Report_Sample.txt" lR = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row For i = 2 To lR For c = 1 To 6 Text = Text & Sheets(1).Cells(i, c).Value If c < 6 Then Text = Text & "; ;" Else Text = Text & vbNewLine End If Next Next Set fs = CreateObject("Scripting.FileSystemObject") Set obj = fs.CreateTextFile(Folder & FileName, True) obj.WriteLine (Text) obj.Close Set fs = Nothing End Sub
xace
The values in the text file don't appear to match those in the Excel worksheet.
This code will write the data to a text file using ;; as a delimiter.
Dim strLine As String Dim rng As Range Set rng = Worksheets("Sheet1").Range("A2") Open "C:\test\MySample1.txt" For Output As #1 Do Until rng.Value = "" strLine = Join(Application.Transpose(Application.Transpose(rng.Resize(, 6).Value)), ";;") Print #1, strLine Set rng = rng.Offset(1) Loop Close #1
If posting code please use code tags, see here.
thanks everyone for the quick response, i'll try it out and see if everything is fine
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks