About Us

p45cal[_50_]

replied off-list.
--
p45cal

"Frank Situmorang" wrote:

Dear Pascal:

I have tested the macro so many times, I tried to wait it, in fact it
worked, but it is very long, could you telll me why?, My be in the process of
my making it to value I need to add VBA screen updating is false, coz, it
will take memory?.

Wht is your idea.

Thanks in advance,

Frank

"p45cal" wrote:

The line
FrankctoValue wbCurrent
is correct and doesn't need adjusting. It just calls your (slightly
adjusted) macro with the parameter wbCurrent, which is the workbook made into
an object (not a value) which is then processed by your macro.
The fact that you can run the blah macro and select hundreds of files at
once means that perhaps there won't need to be 4 clrecks (clerks?) to do this
work.

"the only thing left it the VBA to let me choose/open the file/workbook
from a folder"

!!??!

That's precisely what blah does!! It open a familiar Windows Open file
dialogue box where you can navigate to any folder and select files using the
usual selecting methods (holding the shift key down while selecting, holding
the control key down while clicking to add/remove files that you want to
process, using the Ctrl+A key combination to select all files etc. etc. You
ONLY have to run blah, because it will call FrankcToValue.

It should be fairly safe to try this on your files as at the moment the
original files are not changed, only new ones, with a slightly different
name, are created - and you can easily identify which those are in order to
delete them if you want.

It's up to you now to get FrankcToValue exactly right, doing everything you
want it to do to the files.
--
p45cal

"Frank Situmorang" wrote:

Hi P45cal!, Interesting to hear that we might be doing 4 clrecks out of a
job, could you give me more explanantion, What do you mean by FrankctoValue
wbCurrent . Do you mean that I will put my macro in that line?

Actually I have tested that coverting the sheet into value is ok, the only
thing left it the VBA to let me choose/open the file/workbook from a folder.

I appreciate your help.

Frank

"p45cal" wrote:

I might be doing 4 clercks (sic) out of a job!

Try this, but test it first and adjust as required.

Sub blah()
Application.ScreenUpdating = False
Dim wbCurrent As Workbook
files_to_open = _
Application.GetOpenFilename("Excel files (*.xls), _*.xls", , , , True)
If Not IsArray(files_to_open) Then
MsgBox "Nothing selected"
Exit Sub
Else
For i = LBound(files_to_open) To UBound(files_to_open)
Set wbCurrent = Workbooks.Open(files_to_open(i))
Application.StatusBar = "Processing " & files_to_open(i)
FrankctoValue wbCurrent 'do stuff here
NewFilename = Left(files_to_open(i), Len(files_to_open(i)) - 4) _
& " - Testing - please delete.xls"
wbCurrent.SaveAs NewFilename
wbCurrent.Close
Next i
End If
Set wbCurrent = Nothing
Application.StatusBar = False
Application.ScreenUpdating = True
MsgBox UBound(files_to_open) - LBound(files_to_open) + _
1 & " files processed (hopefully)."
End Sub

Sub FrankctoValue(myWb As Workbook)
'
' FrankctoValue Macro
' Macro recorded 6/27/2007 by Frank
'
' Keyboard Shortcut: Ctrl+s
'
myWb.Activate
Sheets("PO New (2)").Copy After:=Sheets(2)
'Sheets(1).Copy After:=Sheets(1) ' my testing, Pascal
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Columns("A:AV").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Cells.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Selection.Replace What:="", Replacement:="$", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="$", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Use it by running the macro 'blah', which in turn calls your macro, so don't
use your shortcut, create another one to blah instead if you want.

It will ask you to select one or multiple xls files and process them all.
The files don't need to be open, in fact they shouldn't be open.
Progress can be followed as it will show which file is being processed on
excel's status bar (at the bottom).
I have changed your FrankcToValue macro slightly to take a parameter.
It currently saves the files under a new name, which is the original name
with 'Testing - Please delete' tacked on the end, in the same folder as the
original file. Amend as required.
I did this on XL2003.

Note: I was caught out by a Gotcha with GetOpenFilename, it's unlikely to
affect you if you run blah from a keyboard shortcut. Tom Ogilvy has seen it
before, it's at
http://www.excelforum.com/archive/in.../t-497214.html

Pascal

--
p45cal

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Choose File To SaveAs	John[_88_]	Excel Programming	6	September 23rd 06 07:06 PM
Get data from ext. file when i choose an option from combobox	hmoner	Excel Programming	0	November 17th 05 05:16 PM
Import Txt file via vba but still choose column types	Job	Excel Programming	1	July 26th 05 11:42 PM
Drop-Down List to Choose File to Open	tbchief[_2_]	Excel Programming	0	May 28th 04 08:22 PM
Choose file for query	Hasan Cansü[_2_]	Excel Programming	0	February 23rd 04 09:43 AM

Menu

About Us