To let us choose the file
 

Dear Experts:
I want to make an Addin, to perform conversion of worksheet to value which
can be done by 4 clercks. My question is how can we make it LET US CHOOSE the
file, the name of the sheet is the same for all workbooks;

This is my macro which I will make it as an Addin. As you CAN see below, the
clerck will open the worksheet first named(' E10-7-012 -DAIHATSU PAINT.xls").
but this is variable, because there are hundred of files that they will do
the same process, coz I will write again a macro to pick up those values.
Sub FrankctoValue()
'
' FrankctoValue Macro
' Macro recorded 6/27/2007 by Frank
'
' Keyboard Shortcut: Ctrl+s
'
Windows("E10-7-012 -DAIHATSU PAINT.xls").Activate
Sheets("PO New (2)").Copy After:=Sheets(2)
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

I appreciate your precious help.

Frank

To let us choose the file
 

You can get file name using code below. This will open a pop up box so the
file can be selected. You still need to open the file after getting the name.

fileToOpen = Application _
.GetOpenFilename("Excel Files (*.xls),*.xls")
If fileToOpen < False Then
MsgBox "Open " & fileToOpen
End If


"Frank Situmorang" wrote:

Dear Experts:
I want to make an Addin, to perform conversion of worksheet to value which
can be done by 4 clercks. My question is how can we make it LET US CHOOSE the
file, the name of the sheet is the same for all workbooks;

This is my macro which I will make it as an Addin. As you CAN see below, the
clerck will open the worksheet first named(' E10-7-012 -DAIHATSU PAINT.xls").
but this is variable, because there are hundred of files that they will do
the same process, coz I will write again a macro to pick up those values.
Sub FrankctoValue()
'
' FrankctoValue Macro
' Macro recorded 6/27/2007 by Frank
'
' Keyboard Shortcut: Ctrl+s
'
Windows("E10-7-012 -DAIHATSU PAINT.xls").Activate
Sheets("PO New (2)").Copy After:=Sheets(2)
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

I appreciate your precious help.

Frank

To let us choose the file
 

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


"Frank Situmorang" wrote:

Dear Experts:
I want to make an Addin, to perform conversion of worksheet to value which
can be done by 4 clercks. My question is how can we make it LET US CHOOSE the
file, the name of the sheet is the same for all workbooks;

This is my macro which I will make it as an Addin. As you CAN see below, the
clerck will open the worksheet first named(' E10-7-012 -DAIHATSU PAINT.xls").
but this is variable, because there are hundred of files that they will do
the same process, coz I will write again a macro to pick up those values.
Sub FrankctoValue()
'
' FrankctoValue Macro
' Macro recorded 6/27/2007 by Frank
'
' Keyboard Shortcut: Ctrl+s
'
Windows("E10-7-012 -DAIHATSU PAINT.xls").Activate
Sheets("PO New (2)").Copy After:=Sheets(2)
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

I appreciate your precious help.

Frank

To let us choose the file
 

Thanks Joel, it works in letting me chcose the file, but after I open it, it
stopped here in line "Sheets("PO New (2)").Copy After:=Sheets(2)

Could you give me more explanation?

Frank

Keyboard Shortcut: Ctrl+s
'
fileToOpen = Application _
.GetOpenFilename("Excel Files (*.xls),*.xls")
If fileToOpen < False Then
MsgBox "Open " & fileToOpen
End If
Sheets("PO New (2)").Copy After:=Sheets(2)

"Joel" wrote:

You can get file name using code below. This will open a pop up box so the
file can be selected. You still need to open the file after getting the name.

fileToOpen = Application _
.GetOpenFilename("Excel Files (*.xls),*.xls")
If fileToOpen < False Then
MsgBox "Open " & fileToOpen
End If


"Frank Situmorang" wrote:

Dear Experts:
I want to make an Addin, to perform conversion of worksheet to value which
can be done by 4 clercks. My question is how can we make it LET US CHOOSE the
file, the name of the sheet is the same for all workbooks;

This is my macro which I will make it as an Addin. As you CAN see below, the
clerck will open the worksheet first named(' E10-7-012 -DAIHATSU PAINT.xls").
but this is variable, because there are hundred of files that they will do
the same process, coz I will write again a macro to pick up those values.
Sub FrankctoValue()
'
' FrankctoValue Macro
' Macro recorded 6/27/2007 by Frank
'
' Keyboard Shortcut: Ctrl+s
'
Windows("E10-7-012 -DAIHATSU PAINT.xls").Activate
Sheets("PO New (2)").Copy After:=Sheets(2)
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

I appreciate your precious help.

Frank

To let us choose the file
 

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


"Frank Situmorang" wrote:

Dear Experts:
I want to make an Addin, to perform conversion of worksheet to value which
can be done by 4 clercks. My question is how can we make it LET US CHOOSE the
file, the name of the sheet is the same for all workbooks;

This is my macro which I will make it as an Addin. As you CAN see below, the
clerck will open the worksheet first named(' E10-7-012 -DAIHATSU PAINT.xls").
but this is variable, because there are hundred of files that they will do
the same process, coz I will write again a macro to pick up those values.
Sub FrankctoValue()
'
' FrankctoValue Macro
' Macro recorded 6/27/2007 by Frank
'
' Keyboard Shortcut: Ctrl+s
'
Windows("E10-7-012 -DAIHATSU PAINT.xls").Activate
Sheets("PO New (2)").Copy After:=Sheets(2)
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

I appreciate your precious help.

Frank

To let us choose the file
 

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

To let us choose the file
 

thank you very much Pascal, please again if you could do us your favor to
explain the following questions:
1. What is the meaning of _4 in this line:NewFilename =
Left(files_to_open(i), Len(files_to_open(i)) - 4) _
2. Can I use this macro as an Addin?
3. I tried to do one workbook it is OK, but when I run again for the 2nd
time it looks like hang, it says it's processing, but never finished so I
reboot my computer
4. the most important thing, how can I make it work as an additinal sheet to
the original workbook, so it is not in the testing one
5, Can we take the file from any other folders? or should be in one folder.

Thanks in advance,

Frank
Greetings from Jakarta, Indonesia

"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

To let us choose the file
 

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