Solved

Need VBS Script to clean up spread sheet

Posted on 2009-05-11
29
629 Views
Last Modified: 2013-11-10
Hi all,

I am in need of a VBS (Not VBA) script that fill open D:\MYFILE.XLS, Select the entire workbook by name (i.e. "My Sheet") and remove (trim) all white spaces from every column including hide all unused columns and rows then unselect the entire worksheet and position me back to A:2 then save to the same file.


Thanks!
0
Comment
Question by:smyers051972
  • 15
  • 8
  • 3
  • +1
29 Comments
 
LVL 6

Expert Comment

by:EoDawg
ID: 24359222
is it that you want to launch the script external to excel? You can embed the macro, and have the VBS open and start the macro.

So the code would sit in the excel file but it would be the VBS that launched it. let me know if that's an option.
0
 
LVL 1

Author Comment

by:smyers051972
ID: 24359316
Id have to load it every time so its just easier to use VBS :)
0
 
LVL 12

Expert Comment

by:Krys_K
ID: 24369249
Hi There
Is it possible you could upload a sample spreadhseet you want to clean up then it will save me a bit of time when i write something in VBS for you.
Cheers
Krystian
It doesn't have to be the actual spreadsheet, just a sample will do but explain what its to look like at the end result too. :-)
0
 
LVL 1

Author Comment

by:smyers051972
ID: 24369436
0
 
LVL 1

Author Comment

by:smyers051972
ID: 24369478
looking just to trim all white spaces from all columns, this will reduce the spreadsheet size by a little, maybe not a lot however when you pull this data across a satellite connection every little bit can help!
0
 
LVL 13

Expert Comment

by:StellanRosengren
ID: 24400288
Hi
Could this be something useful?

Kind regards,
Stellan
Dim objWorkbook

Dim objWks

Dim rngCell

Dim objExcel
 

Set objExcel = CreateObject("Excel.Application")

Set objWorkbook = objExcel.Workbooks.Open("D:\MYFILE.xls")

Set objWks=objWorkbook.Worksheets(1)

    

For Each rngCell In objWks.UsedRange

	rngCell.Value = Trim(rngCell.Value)

Next rngCell

Open in new window

0
 
LVL 1

Author Comment

by:smyers051972
ID: 24412391
Hi there,

Sorry for the late responce, I will test it and update accordingly.

Thank you for your help!
0
 
LVL 1

Author Comment

by:smyers051972
ID: 24414390
I tested and got an expected end of statement error on line 12 char 6.
0
 
LVL 13

Expert Comment

by:StellanRosengren
ID: 24417101
Sorry,
I am not so used to vbscript. Please remove 'rngCell' from that line.

Stellan
0
 
LVL 1

Author Comment

by:smyers051972
ID: 24423213
Hello,

I did what was recommended and tested now we arent erroring out, but oddly now I see no CPU consumption and it stays open indefinately.

Thanks!
0
 
LVL 13

Expert Comment

by:StellanRosengren
ID: 24423767
Hi smyers051972,

Would it be possible for you to upload a sample workbook that you have tried this on?

Kind regards,
Stellan
0
 
LVL 1

Author Comment

by:smyers051972
ID: 24424026
0
 
LVL 1

Author Comment

by:smyers051972
ID: 24424033
http://filedb.experts-exchange.com/incoming/2009/05_w20/137798/1.newplayers.xls
to be more spcific, names removed to protect the innocent ;)
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 13

Expert Comment

by:StellanRosengren
ID: 24425733
Hi,
The code is looping through all cells in the used range of the sheet and reads each value, trims it and writes it back to the cell. It seems to be working alright, even on your sheet. BUT, it is not fast enough. You have more than 200 000 cells in the sheet and it looks like it takes hours to finish. Of course not acceptable. I will try to find something better. Please be patient.

Kind regards,
Stellan
0
 
LVL 1

Author Comment

by:smyers051972
ID: 24425818
Thank you very much I will keep checking back over the next day or two!
0
 
LVL 13

Accepted Solution

by:
StellanRosengren earned 500 total points
ID: 24427022
Hi again,

Now I have done some more testing. A much faster method to trim the cells is to use a worksheet array formula with the worksheet Trim function. Then you can treat a whole column at a time. The formula is evaluated by using the Evaluate method of the Excel.Application object.
When I run the attached script on your sample worksheet it finished after 15 seconds.
I hope this will work for you.

Kind regards,
Stellan
Dim objWorkbook

Dim objWks

Dim rngCol

Dim objExcel

 

Set objExcel = CreateObject("Excel.Application")

Set objWorkbook = objExcel.Workbooks.Open("E:\newplayers.xls")

Set objWks = objWorkbook.Worksheets("newplayers.xls")

    

    

    

    For Each rngCol In objWks.UsedRange.Columns

        rngCol.Value = objExcel.Evaluate("IF(ROW(" & rngCol.Address & "),IF(" & rngCol.Address & "<>"""",TRIM(" & rngCol.Address & "),""""))")

    Next

	objExcel.Visible=True

Open in new window

0
 
LVL 1

Author Comment

by:smyers051972
ID: 24432221
Hello

Close! The only two things I dont see it doing is saving the workbook and excel is popping up on me, I dont remember the way to make it stay hidden with no prompts or how to make it save the file (same file)

Thanks!
0
 
LVL 13

Expert Comment

by:StellanRosengren
ID: 24433279
Hi,
No problems,
you see the last line 'objExcel.Visible=True' is making Excel pop up.
I used it to be able to examine the results of the script.
You can replace that line by
objWorkbook.Close True
where 'True' is for SaveChanges
Then you should add
objExcel.Quit
set objWks=Nothing
set objWorkbook=Nothing
set objExcel=Nothing
which should prevent this Excel instance from continue running in the background.
I am not sure if it is necessary to set the variables to Nothing but possibly it does free up memory. So, it will not hurt to do that.

Kind regards,
Stellan

0
 
LVL 1

Author Closing Comment

by:smyers051972
ID: 31580333
Thank you again for your help!
0
 
LVL 13

Expert Comment

by:StellanRosengren
ID: 24444659
I am glad that I could help. Actually, this is a solution that I will use myself since it is much faster than the method I normally use.
Thanks for the grade and the points.

Kind regards,
Stellan
0
 
LVL 1

Author Comment

by:smyers051972
ID: 24451877
Not a problem... I am really interested in creating pie graphs from the data in the spreadsheet through VBS code but last time I opened the case no one touched it LOL How about you?
0
 
LVL 12

Expert Comment

by:Krys_K
ID: 24451994
Hi there
Sorry i didn't input on this since my last post, however, when i saw that stellan had given you the answer i decided to stand back and allow him the honours. However, in readinfg your last postabout pie chart, i would be happy to work with Stellan on getting something together if he was interested, if not then i could always give it a go myself. :-)
Regards
Krystian
0
 
LVL 13

Expert Comment

by:StellanRosengren
ID: 24452654
Hi Krystian,
I will be glad to assist.

smyers051972, please post a link here to the question about pie charts.

Kind regards,
Stellan
0
 
LVL 1

Author Comment

by:smyers051972
ID: 24452721
Ill open a new question on it and try to explain what I can, if I dont put enough just request more info and discuss further :)

Thanks!

A link to the new question should be my next follow up on this.
0
 
LVL 12

Expert Comment

by:Krys_K
ID: 24452775
Nice 1. :-)
Will collaborate on the new post.
Krystian
0
 
LVL 1

Author Comment

by:smyers051972
ID: 24452823
0
 
LVL 1

Author Comment

by:smyers051972
ID: 24452832
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now