Trim vba

[shexe]

I need some assistance creating a TRIM function in VBA. I export a table to Excel and run a macro that backfills primary member data for instances of secondary members. I'm hoping to just include a trim function in the Macro, but the function needs to run for the heading columns and content.

I know this is an Access forum but I have found people on here to be very crafty with VBA as well.

Below is my macro.

Sub Macro()
Dim oname 'Output File name
Dim subssn_value
Dim subssnb4_value
Dim subfirst_value
Dim subfirstb4_value
Dim x As Integer
Dim name As String
x = 2 'counter for the row in colum B

Sheets("MemloadMetsLite").Select
For x = 2 To 445 '445 rows
subssnb4_value = Cells(x, 6)
subfirstb4_value = Cells(x, 2)
If subfirstb4_value = "" Then
subfirstb4_value = name
End If
If subssnb4_value = subssn_value Then
Cells(x, 2) = name
End If
subssn_value = subssnb4_value
name = subfirstb4_value
'If subfirstb4_value = "" And name <> Null Then
'Cells(x, 2) = name


'End If

[hertfordkc]

It sounds as if you are exporting data to Excel just to do some stuff that should be done in Access.
1) You could put your Sub or Function in a module1 and call it from forms and queries.
2) You are adding data to a table??? Is it already in an Access table?? Why not import the data into an Access table and run an update query?
3) You can change headings and control labels with VBA. IMHO, you shouldn't plan on changing the field names of anything.

[RayMilhon]

I've never used Excel VBA but Access VBA comes with a Trim Function built in. I would be surprised if Excel didn't also.

[shexe]

Ray - can you add some insight to the TRIM Access VBA function?

HertfordKC - I agree but I cannot make any dramatic changes to this process yet as the group is very wary of any change. Ideally the Excel portion will be migrated to Access in February. As for right now I am patching leaks during their peak season.

[hertfordkc]

The Trim function removes blanks at the beginning or end of a string, e.g.
Trim(" 123 ") will return "123". You can use it in a calculated field of a query, in a control, and of course VBA.
Also, you might look at the Replace function if you have blanks (or other characters) that you need to edit out or replace. Just google VBA Replace.

[RayMilhon]

hertfordkc explained it very well.