Discussion Locked

Trimming text imports to excel with VBA

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

2 part question:
1. I've got a comma delimited text file I work with weekly, which I pull into excel and chart. Being minimally conversant with VBA, I've managed to record and tweak macros for most aspects of this, but there are 4 columns that regularly come down with extra spaces at the end, which stuffs up the charting. Manually, I insert a column, put a trim formula in the blank one then paste values back into the original, deleting the column, but I'd like to automate this too. Does anyone have a subroutine that will eliminate trailing spaces from all filled cells (the range varies from week to week, so I can't just record this process, since if I drag the formula down too far it leaves 'values' which extend the table out and throw the charts off.

2nd part of the question: How do I get a macro to prompt for an entry which will then be used to name the spreadsheet, ie Stats Week 52? If it's possible I'd like just to be prompted for the week number, and the rest stay constant.

Thanks,

Crystal

5 replies

by cnwoods

Thanks, but I'd already found that TrimAll sub and it didn't work. I ended up finding a site at excelforum.com that gave me the code below, which worked perfectly for me, but I still need the code to prompt for the name AND save the file using it. Can you give me further info?

Code below for anyone with the same issue:
Sub TrimSelection()

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

End Sub

Sub TrimRange()
Dim rng As Range
Set rng = Range("A1").CurrentRegion 'change this to suit
For Each c In rng
c.Value = Trim(c)
Next c
End Sub

by dklippert

Sub What()
Answer = InputBox("What is the week number?")
ActiveWorkbook.SaveAs Filename:="C:\FoderName\Stats Week " & Answer & ".xls"
End Sub

by cnwoods

Sorry for delay in rating - these can't be accepted without closing the question off so I didn't want to do this until I'd fully tested out the solution. My routine still isn't quite right, but this part of it runs ok, and I'm putting up a separate question for the next issue (pivot tables DataSource issues). Thanks for helping.

Crystal

This discussion is closed to new replies.