To let us choose the file

[Guest]

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

 

[Guest]

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

 

[Guest]

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/index.php/t-497214.html

Pascal

 

[Guest]

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)

 

[Guest]

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

 

[Guest]

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.

re:"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.

 

[Guest]

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

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.

re:"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.

 

[Guest]

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

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.

re:"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.

 

[Guest]

1. What is the meaning of _4 in this line:NewFilename =
Left(files_to_open(i), Len(files_to_open(i)) - 4) _

It is the continuation character in vba. It means that although there is
more code on the next line, it's really the same line. I've used them so
copying and pasting from the message should work. (This is because message
boards tend to wrap long lines on to the next line.)

2. Can I use this macro as an Addin?
Very very probably, but I'm not about to take you through the process.
Googling gave:
http://www.cpearson.com/excel/CreateAddIn.htm
http://www.jkp-ads.com/articles/DistributeMacro00.htm
http://www.bettersolutions.com/excel/ECA723/NI925918332.htm

Do you really need to create an add-in? See below.

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.

If there's a hang you should try to stop code execution by pressing Ctrl and
Break on the keyboard. Usually a dialogue box comes up where you should
choose 'Debug', whereupon you'll be taken to the vbe where the line it
stopped on is highlighted in yellow. You can then step through the code by
repeated presses of the F8 key to see what's happenning (eg. in a
never-ending loop), and correct accordingly.

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.

This is vba code, not a worksheet. It can be pasted into any workbook. It
operates on other files, not on itself. Instead of creating an add-in, you
could just send a workbook with the code in it and ask them to open it and
run 'blah'.

5, Can we take the file from any other folders? or should be in one folder.

On any single run of 'blah' only files from one folder can be selected. Run
it again to do files in other folders. If later, when debugging is complete,
you want to do a huge run in one go, move the files to be processed into a
single folder first.
Certainly, there will be a way, with more coding, to select multiple folders
and process the files within them, but having made 4 clrecks redundant, do
you want me to be responsible for shutting down a whole department? Post
another question on the discussion group perhaps. I don't have the time, just
now.

 

[Guest]

replied off-list.
--
p45cal

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