Trim() in VBA

[Wayman Bell]

I am running a macro to copy data from excel spreadsheets to paste into an
access table. The problem I occasionally run into is an address that has a
ton of trailing space and is too large to paste into the database table. I'm
sure there must be a snippet of code I can include in my macro to trim the
extra space when copying the data so I do not have to increase the size of
the field in the database to accept to extra nothing.

Does anyone have an idea?

Thanks,

Wayman

 

[Ron Rosenfeld]

I am running a macro to copy data from excel spreadsheets to paste into an
access table. The problem I occasionally run into is an address that has a
ton of trailing space and is too large to paste into the database table. I'm
sure there must be a snippet of code I can include in my macro to trim the
extra space when copying the data so I do not have to increase the size of
the field in the database to accept to extra nothing.

Does anyone have an idea?

Thanks,

Wayman

Uh, VBA has several TRIM functions. Why not use those?

==============================
LTrim, RTrim, and Trim Functions

Returns a Variant (String) containing a copy of a specified string without
leading spaces (LTrim), trailing spaces (RTrim), or both leading and trailing
spaces (Trim).

Syntax

LTrim(string)

RTrim(string)

Trim(string)

The required string argument is any valid string expression. If string contains
Null, Null is returned
============================


--ron

 

[Vince]

I don't get it. Why not just use the Trim() function??
Eg.
dim Abc as string
Abc="This is a string
"

Abc=trim(Abc)
' Now Abc="This is a string"
' Then you store Abc in the database.....

Do you have code that is not working? If so, why don't you post it....

 

[Wayman Bell]

I am trying this but I keep getting a compile error with the red Trim highlighted.

Dim MyString As String
Dim TrimString As String

Range("D5").Select
MyString = Range("D5").Value
TrimString = Trim(MyString)
Selection.Copy

Compile error: Wrong number of arguments or invalid property assignment

 

[Vince]

Not tested, but this is worth a shot:


Dim MyString As String
Dim TrimString As String

Range("D5").Select
MyString = Range("D5").Value
TrimString = Trim$(MyString)
Selection.Copy


I am trying this but I keep getting a compile error with the red Trim highlighted.

Dim MyString As String
Dim TrimString As String

Range("D5").Select
MyString = Range("D5").Value
TrimString = Trim(MyString)
Selection.Copy

Compile error: Wrong number of arguments or invalid property assignment

 

[Wayman Bell]

Same error.

Private Sub cmdTrim_Click()
Dim MyString As String
Dim TrimString As String

Range("D5").Select
MyString = Range("D5").Value
TrimString = Trim$(MyString)
Selection.Copy
End Sub
Not tested, but this is worth a shot:


Dim MyString As String
Dim TrimString As String

Range("D5").Select
MyString = Range("D5").Value
TrimString = Trim$(MyString)
Selection.Copy


I am trying this but I keep getting a compile error with the red Trim highlighted.

Dim MyString As String
Dim TrimString As String

Range("D5").Select
MyString = Range("D5").Value
TrimString = Trim(MyString)
Selection.Copy

Compile error: Wrong number of arguments or invalid property assignment

 

[Ron Rosenfeld]

I am trying this but I keep getting a compile error with the red Trim highlighted.

Dim MyString As String
Dim TrimString As String

Range("D5").Select
MyString = Range("D5").Value
TrimString = Trim(MyString)
Selection.Copy

Compile error: Wrong number of arguments or invalid property assignment

I do not get that error message. Perhaps there is code that you have not
posted that is causing the problem.

==================
Sub foo()
Dim MyString As String
Dim TrimString As String

Range("D5").Select
MyString = Range("D5").Value
TrimString = Trim(MyString)
Selection.Copy

Debug.Print TrimString & " " & Len(TrimString) & " characters"

End Sub
===================

With " abc " in D5, the above prints

abc 3 characters

in the immediate window.

A few comments:

1. There is no need to Select D5 before operating on it.
2. Your Selection.Copy command
a. has no destination to Paste
b. will copy the UNmodified contents of D5. This may not be what you
want.

If you want to TRIM a range of cells in place, you might try something like:

===============================
Sub foo()
Dim c As Range

'first select the range to be operated on

For Each c In Selection
c.Value = Trim(c.Value)
Next c

End Sub
=======================

or, by hard-coding a range:

==========================
Sub foo()
Dim c As Range
Dim rng As Range

Set rng = [D110]

For Each c In rng
If Not IsError(c.Value) Then
c.Value = Trim(c.Value)
End If
Next c

End Sub
======================


--ron

 

[Alan]

I am trying this but I keep getting a compile error with the red Trim
highlighted.

Dim MyString As String
Dim TrimString As String

Range("D5").Select
MyString = Range("D5").Value
TrimString = Trim(MyString)
Selection.Copy

Compile error: Wrong number of arguments or invalid property
assignment

Works for me with string, number, or formula that evaluates to a
string or number is D5.

I do get a type mismatch on the MyString = Range("D5").Value line if
D5 evaluates to, say, #NA but you are getting past that line you said.

What if you step through the code, what is the exact value of MyString
just before the line with the TRIM function runs?

Alan.

 

[Wayman Bell]

In cell D5 I have my name with a dozen trailing spaces. When I run in immediate window I get

?Range("D5").Value
Wayman
?MyString


This is all the code I have behind a command button. I get hundreds of these spreadsheets each day to process and I thought this would be the easiest fix. If I can't figure this out I may have to send out a new form that uses the formula =Trim(D5)

Sub cmdTrim_Click()
Dim MyString As String
Dim TrimString As String

Range("D5").Select
MyString = Range("D5").Value
TrimString = Trim(MyString)
Selection.Copy
End Sub

I get the same error whether I have Trim(MyString) or Trim(Range("D5").Value)

My aim is to trim and copy the contents of cell D5 to the clipboard then I swap to the database table and Ctl+V to paste.
I know I am probably just doing something stupid and it should be a pretty simple process. I do appreciate everyone's helpful ideas.

Wayman

 

[Vince]

I am not sure if this is going to help but I vaguely remember that I had a similar problem ages back. I fiddled around with "Tools - References" and I eithe had a missing reference or I did not have a reference to the Office library or a reference to MS VBA for applications. That solved the problem for me.

However, I am not able to reproduce the problem.

Just my two cents. Good luck!
In cell D5 I have my name with a dozen trailing spaces. When I run in immediate window I get

?Range("D5").Value
Wayman
?MyString


This is all the code I have behind a command button. I get hundreds of these spreadsheets each day to process and I thought this would be the easiest fix. If I can't figure this out I may have to send out a new form that uses the formula =Trim(D5)

Sub cmdTrim_Click()
Dim MyString As String
Dim TrimString As String

Range("D5").Select
MyString = Range("D5").Value
TrimString = Trim(MyString)
Selection.Copy
End Sub

I get the same error whether I have Trim(MyString) or Trim(Range("D5").Value)

My aim is to trim and copy the contents of cell D5 to the clipboard then I swap to the database table and Ctl+V to paste.
I know I am probably just doing something stupid and it should be a pretty simple process. I do appreciate everyone's helpful ideas.

Wayman

 

[Ron Rosenfeld]

This is all the code I have behind a command button. I get hundreds of these spreadsheets each day to process and I thought this would be the easiest fix. If I can't figure this out I may have to send out a new form that uses the formula =Trim(D5)

Sub cmdTrim_Click()
Dim MyString As String
Dim TrimString As String

Range("D5").Select
MyString = Range("D5").Value
TrimString = Trim(MyString)
Selection.Copy
End Sub

I cannot reproduce your error message. However, let me repeat that your code
will NOT copy the modified (TRIMmed) string to the clipboard! Rather it will
copy the UNmodified string.

Try this:

=================
Sub cmdTrim_Click()
Range("D5").Value = Trim(Range("D5").Value)
Range("D5").Copy
End Sub
================


--ron

 

[Wayman Bell]

That's strange, you can't reproduce the error and I can't get around it. I will try on another computer tomorrow to see if that changes anything. In the meantime I added =Trim() to the problem areas of the forms and will replace the ones in the field that are causing problems. I still have to make this work in VBA on my end though, curiosity I guess.

Thanks for everyone's help.

Wayman
I am not sure if this is going to help but I vaguely remember that I had a similar problem ages back. I fiddled around with "Tools - References" and I eithe had a missing reference or I did not have a reference to the Office library or a reference to MS VBA for applications. That solved the problem for me.

However, I am not able to reproduce the problem.

Just my two cents. Good luck!
In cell D5 I have my name with a dozen trailing spaces. When I run in immediate window I get

?Range("D5").Value
Wayman
?MyString


This is all the code I have behind a command button. I get hundreds of these spreadsheets each day to process and I thought this would be the easiest fix. If I can't figure this out I may have to send out a new form that uses the formula =Trim(D5)

Sub cmdTrim_Click()
Dim MyString As String
Dim TrimString As String

Range("D5").Select
MyString = Range("D5").Value
TrimString = Trim(MyString)
Selection.Copy
End Sub

I get the same error whether I have Trim(MyString) or Trim(Range("D5").Value)

My aim is to trim and copy the contents of cell D5 to the clipboard then I swap to the database table and Ctl+V to paste.
I know I am probably just doing something stupid and it should be a pretty simple process. I do appreciate everyone's helpful ideas.

Wayman

 

[Dave Peterson]

Did you follow Vince's suggestion about Tools|References.

Look for MISSING in that dialog.

If you find one, uncheck that reference (or change it to point at the correct
file).

 

[Wayman Bell]

I looked and the two he mentioned are checked but there are about a 1000
other references that are not. I tried unchecking some of them then run the
macro. When I went back to tools|references they were checked again.
Microsoft Office 10 Object Library. and Microsoft Forms 2.0. Object Library.
I could not uncheck VBA because it was in use.

I'll keep looking.

 

[Otto Moehrbach]

Wayman
There are two Trim functions, one in VBA and one in the worksheet. They
are not the same. The worksheet trim does leading and trailing spaces but
also removes all excess spaces between words. IOW, it will leave one space
between words. I have found it more convenient, in VBA, to just use the
worksheet function.
To use the worksheet Trim function in VBA, use the following line:
MyTrim = Application.Trim(Range("A5"))
Note that you have to specify a range, not just a cell address like you
would in the worksheet function. HTH Otto

 

[Wayman Bell]

Thanks Otto,

That does what I needed!

Dim MyTrim As String
MyTrim = Application.Trim(Range("F24"))
Range("H25").Value = MyTrim

Wayman

 

[Otto Moehrbach]

Wayman
You can shorten that if you wish. Use:
Range("H25").Value = Application.Trim(Range("F24"))
HTH Otto

 

[Wayman Bell]

Thanks, I'll do that.

Wayman

Wayman
You can shorten that if you wish. Use:
Range("H25").Value = Application.Trim(Range("F24"))
HTH Otto