Trying to generate a .txt file by using macros to copy the results from excel to text file.

Question

0.00/5 (No votes)

See more:

VB

Sub Textfile()

Dim FilePath As String
Dim CellData As String
Dim LastCol As Long
Dim LastRow As Long

LastCol = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column
LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
CellData = ""
DefaultFilePath = "xyz"

FilePath = Application.DefaultFilePath & "\RPA.txt"
Open FilePath For Output As #2
For i = 1 To LastRow
   For j = 1 To LastCol
      If j = LastCol Then
         CellData = CellData + Trim(ActiveCell(i, j).value)
      Else
         CellData = CellData + Trim(ActiveCell(i, j).value) + "  "
      End If
   Next j
   Write #2, CellData
   CellData = ""
Next i
Close #2

MsgBox ("Done")

End Sub

What I have tried:

In this particular line

VB

CellData = CellData + Trim(ActiveCell(i, j).value) + "  "

I am getting data type mismatch error, I have pasted the whole code in the description, what could be the possible error.

Posted 11-Apr-18 2:01am

Member 13773934

Updated 11-Apr-18 7:12am

Wendelius

v4

Add a Solution

Comments

Richard MacCutchan 11-Apr-18 8:50am

It most likely means that the cell content value is not a string, so you cannot concatenate another string to it.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mehdi Gholam · Answer 1 · 2018-04-11T02:53:00

Solution 1

Try the following :

VB

CellData = CellData + Trim("" + ActiveCell(i, j).value) + "  "

Posted 11-Apr-18 2:53am

Mehdi Gholam

Comments

Member 13773934 12-Apr-18 6:42am

I tried the Code but same thing is happened, and actually i have numeric values as well in that line Item so what kind of Data type I can use instead of string.

Wendelius · Answer 2 · 2018-04-11T07:12:00

If you're trying to concatenate the data from the cell try using & operator. The cell may contain a numeric value which would cause an error if a numeric value is trying to be added to a string. You can also use CStr if you like.

In other words

CellData = CellData & CStr(Trim(ActiveCell(i, j).value)) & "  "