smyers051972
asked on
Need VBS Script to clean up spread sheet
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!
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!
ASKER
Id have to load it every time so its just easier to use VBS :)
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. :-)
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. :-)
ASKER
Absolutely.
Its actually apart of this same item:
https://www.experts-exchange.com/questions/24393435/Modification-Help-VBS-Script-excel-2003.html
Its actually apart of this same item:
https://www.experts-exchange.com/questions/24393435/Modification-Help-VBS-Script-excel-2003.html
ASKER
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!
Hi
Could this be something useful?
Kind regards,
Stellan
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
ASKER
Hi there,
Sorry for the late responce, I will test it and update accordingly.
Thank you for your help!
Sorry for the late responce, I will test it and update accordingly.
Thank you for your help!
ASKER
I tested and got an expected end of statement error on line 12 char 6.
Sorry,
I am not so used to vbscript. Please remove 'rngCell' from that line.
Stellan
I am not so used to vbscript. Please remove 'rngCell' from that line.
Stellan
ASKER
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!
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!
Hi smyers051972,
Would it be possible for you to upload a sample workbook that you have tried this on?
Kind regards,
Stellan
Would it be possible for you to upload a sample workbook that you have tried this on?
Kind regards,
Stellan
ASKER
ASKER
http://filedb.experts-exchange.com/incoming/2009/05_w20/137798/1.newplayers.xls
to be more spcific, names removed to protect the innocent ;)
to be more spcific, names removed to protect the innocent ;)
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
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
ASKER
Thank you very much I will keep checking back over the next day or two!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
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
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
ASKER
Thank you again for your help!
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
Thanks for the grade and the points.
Kind regards,
Stellan
ASKER
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?
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
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
Hi Krystian,
I will be glad to assist.
smyers051972, please post a link here to the question about pie charts.
Kind regards,
Stellan
I will be glad to assist.
smyers051972, please post a link here to the question about pie charts.
Kind regards,
Stellan
ASKER
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.
Thanks!
A link to the new question should be my next follow up on this.
Nice 1. :-)
Will collaborate on the new post.
Krystian
Will collaborate on the new post.
Krystian
ASKER
ASKER
I dont think that will work...
Here is the link sorry!
https://www.experts-exchange.com/questions/24431686/Would-like-to-use-VBS-to-create-Pie-Graphs-inside-excel.html
Here is the link sorry!
https://www.experts-exchange.com/questions/24431686/Would-like-to-use-VBS-to-create-Pie-Graphs-inside-excel.html
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.