Posted to microsoft.public.excel.programming
|
|
EXCEL VBA question
Thanks for your help on the save stuff, Dave. As far as viewing my macro as
it runs, it seems like nobody wants to let me see my macro at work! How
hard is it to have it show me the steps?
Thanks
G
"Dave Peterson" wrote in message
...
wbSource.Saved = true
doesn't save the workbook. It just tells excel to consider the workbook
"clean"--no changes since the last save.
wbSource.Save 'would actually save the workbook.
wbSource.Saved = true
'followed by
wbSource.close
means you won't get prompted with "Do you want to save changes to your
workbook?" right before it closes.
I like this line as an alternative:
wbSource.close savechanges:=false
=======
When I'm testing code, I'll put some breakpoints in the code (to stop it
when it
hits a certain line). Then I'll go look at excel to see if things are
progressing ok.
You could also step through the code with F8's (inside the VBE).
Grace wrote:
OK, it took some thinking but it's working now. Actually, I don't even
want
to close the source file, as I need to compare my new target template
result
to it, to make sure the new one is processing the scenario data the same
as
before. Also, I note that you had
wbSource.Saved = True
and I'm not sure why I would want to do that. I'm not changing the
source,
only the target file, right? And if I inadvertently did, I wanted want
to
save those changes. Right? Is your command intended to accomplish
something else?
Though your approach is very quick and clean, the only thing I don't
like is
that I don't see the commands being carried out. Often this is my way
of
realizing the macro isn't working right, for some odd case (and in
initially
troubleshooting the macro). Is there any easy way to allow me to see
the
files as this is being carried out. I can afford the extra 100
milliseconds
it might cost me.
Thanks so much for your great help!
G
"Harald Staff" wrote in message
...
Hi Grace
Lots of things here. I'll try:
First, do I replace the name of the worksheet I want to copy from in
place
of "Sheets(1)".
wbSource.Sheets("Inputs").Range("C11:E12").Copy
Also, do I need to use your:
wbSource.Close
after each paste operation, or just at the end?
Well, it closes the newly opened workbook. If you use this in a loop
with
multiple workbook openings then wbSource will be reassigned to a new
file
each time and your code won't keep track of the rest of them. But if
you
copy several ranges from a single workbook, don't close it until all
are
done.
Also what would be the command for unprotecting a worksheet
before copying?
wbTarget.Sheets("Inputs").Unprotect
'actions
wbTarget.Sheets("Inputs").Protect
or with a passwords:
wbTarget.Sheets("Inputs").Unprotect ("GraceLockedMe")
'actions
wbTarget.Sheets("Inputs").Protect ("GraceLockedMe")
HTH. Best wishes Harald
"Grace" skrev i melding
...
This certainly seems a lot quicker and I think I realize you don't
want
to
teach me inefficient programming. However, what little I do know is
just
that so, going this route will require more clarifications, so
please
bear
with me!
First, do I replace the name of the worksheet I want to copy from in
place
of "Sheets(1)". I tried that for a worksheet named "inputs" as
follows:
wbSource.Inputs.Range("C11:E12").Copy
wbTarget.Inputs.Range("C11:E12")
and it bombed the macro. If not, how do I identify the worksheet
name?
Also, do I need to use your:
wbSource.Close
after each paste operation, or just at the end? If not, what is
this
command doing? Also what would be the command for unprotecting a
worksheet
before copying?
Thanks,
G
"Harald Staff" wrote in message
...
Grace
I didn't answer your question because I believed it was the wrong
solution
to a problem. What you want done is to copy from one place to
another,
not
to activate windows. So I still won't tell you, but see if this
does
what
you want:
Sub test()
Dim F As Variant
Dim wbSource As Workbook
Dim wbTarget As Workbook
Set wbTarget = ThisWorkbook 'or ActiveWorkbook
F = Application.GetOpenFilename("Workbooks (*.xls), *.xls", _
, "Select a file to copy into:")
If F = False Then Exit Sub
Set wbSource = Workbooks.Open(F)
wbSource.Sheets(1).Range("A1:F14").Copy
wbTarget.Sheets(2).Range("D1:I14")
wbSource.Saved = True
wbSource.Close
End Sub
OR: Do you need to manually select a range in the middle of your
macro
?
HTH. Best wishes Harald
"Grace" skrev i melding
...
Perhaps I am confused but I don't think you have answered my
question.
What
I do is copy data from a source file to the same location in a
destination
file. I do this for many different areas on many different
tabs.
So, I go to the source file (which you have called F) and I
highlight,
say,
worksheet tab 1, then a range of cells on that tab. Then I
activate
the
destination file, find the same tab and same cell area and paste
it.
Then, I go back to the source file and repeat for another tab
and
area.
So,
each time I am toggling between two files. I was trying to find
a
way
to
call the source file each time. I thought the command:
Windows(F.xls).Activate
would work but it bombs the macro out. What is the right syntax
to
toggle
back to that sheet?
Thanks,
Grace
"Harald Staff" wrote in message
...
"Grace" skrev i melding
...
I know that before
each copy operation, I don't want to re-open the file, but
would
rather
activate it.
Usually one doesn't have to select or activate anything to
move/copy
stuff
from one place to another. It just slows things down while
looking
ugly.
Best wishes Harald
--
Dave Peterson
|