Trimming whitespace after pulling data from an Excel cell

[BonnieG]

Hello people

I've got a piece of VBA scripting which runs as an event linked to a button on my MS Access form.

I maintain a database of members of staff at my organisation. It's pretty outdated...

I'm basically wanting to pull in their updated data (extracted from on our payroll system) from a spreadsheet, into a form, when clicking a button on a particular person's record.

This is what I have so far.

start = Forms!frm_main2!txt_start_row.Value - 1
conv = DDEInitiate("EXCEL", "Staff List.xlsx")
cell_employee_number = "R" & start & "C1"
cell_surname = "R" & start & "C2"
cell_forename = "R" & start & "C3"
cell_job_role = "R" & start & "C5"
cell_manager = "R" & start & "C6"
cell_department = "R" & start & "C7"
cell_email = "R" & start & "C8"
new_employee_number = CStr(DDERequest(conv, cell_esr_number))
new_surname = CStr(DDERequest(conv, cell_surname))
new_forename = CStr(DDERequest(conv, cell_forename))
new_job_role = CStr(DDERequest(conv, cell_job_role))
new_manager = CStr(DDERequest(conv, cell_manager))
new_department = CStr(DDERequest(conv, cell_department))
new_email = CStr(DDERequest(conv, cell_email))

The function "CStr(DDERequest())" converts the cell number into the readable data, however I seem to have whitespace below the value.

What would I need to do to strip out this whitespace? Would I use strtrim? If so, I am unsure of the syntax... how would I incorporate strtrim into the above?

Many thanks in advance for any help offered.

 

[pr2-eugin]

StrTrim is not a function in VBA, AFAIK. You have just Trim which is,

? Trim("    Hello World    ")
Hello World

? RTrim("    Hello World    ")
    Hello World

? LTrim("    Hello World    ") & "-"
Hello World    -

 

[BonnieG]

Thanks - I have just tried this:

new_employee_number = Trim(CStr(DDERequest(conv, cell_employee_number)))

But it doesn't work and I am still getting the white space when passing the value like this:

[employee_number].Value = new_employee_number

The screencap attached is the error I get as I'm trying to put the value into a text box which accepts only 8 characters.

If I try to put it into a large text box it copies fine but with a line break below it which is why it's not copying into the 8-character box.

 

[gemma-the-husky]

the TRIM function strips spaces from the left and right of a string.

a space is ascii chr32.

If your string is not being stripped of all the spaces, then there is something else. The most likely culprit is a carriage return/line feed (chr13 and chr10) - which trim will not remove.

you can replace chr13 and chr10 with blanks easily enough - but you may start losing some characters that you want to retain. You ought to investigate your strings a bit more carefully to see what they actually do contain.

 

[BonnieG]

Thanks Dave.

Unfortunately that didn't work, but I have got it to work with the following...

trimmed_employee_number = Left(new_employee_number, 8)

Thankfully it doesn't error with any of the other fields which are of variable length.

I'll keep an eye on it to make sure I'm not losing characters, but it seems to be working okay so far! Thanks for the help guys!

 

[pr2-eugin]

If you click Debug and then print the value of the error-ing statement, what is the value you get?

 

[CJ_London]

sounds like you have a carriage return in your string

try

trim(replace(mystring,vbcrlf,""))

 

[BonnieG]

sounds like you have a carriage return in your string

try

trim(replace(mystring,vbcrlf,""))

That was the one! That fixed it. I realised it was doing it with my other values too, it just wasn't throwing up an error, but it was inserting the carriage return.

I appreciate everyone's help! Thanks!