Saving using VBA
 

Hi everyone,

I know how to save a spreadsheet using VBA, but I want to move/copy the file
after it has been saved, is this possible?

I am creating a system where spreadsheets get dropped into an Inbox folder
(e.g. c:\Inbox) folder and then moved or copied to an Outbox folder (e.g.
c:\Outbox), once they have been edited and saved.

I wish to attached some code to a button that saves the spreadsheet and then
prompts the user (with a dialogue box), asking if they want to move or copy
the folder to the outbox, and then close the file down.

I understand that the move may not be possible, as the file is stil open,
but the copy (save as) will be possible.

Any help would be much appreciated.

Also, is it possible to save MS word files through excel VBA code??

Thanks in Advance.

Pinda.

Saving using VBA
 

Look at the NAME function.

On Sat, 6 Nov 2004 16:08:02 -0800, "Pinda"
wrote:

Hi everyone,

I know how to save a spreadsheet using VBA, but I want to move/copy the file
after it has been saved, is this possible?

I am creating a system where spreadsheets get dropped into an Inbox folder
(e.g. c:\Inbox) folder and then moved or copied to an Outbox folder (e.g.
c:\Outbox), once they have been edited and saved.

I wish to attached some code to a button that saves the spreadsheet and then
prompts the user (with a dialogue box), asking if they want to move or copy
the folder to the outbox, and then close the file down.

I understand that the move may not be possible, as the file is stil open,
but the copy (save as) will be possible.

Any help would be much appreciated.

Also, is it possible to save MS word files through excel VBA code??

Thanks in Advance.

Pinda.

Saving using VBA
 

Pinda,

You have a number of options.

Personally, I would ask the question before saving and than take the action.
So if they say, Copy, you can do a SaveAs. If they say move, you can do a
Save and a Name, or a Saveas and a Kill on the file in c:\Inbox.

As to saving a word doc, not directly, as Excel saves Excel and various text
formats. Why would you open a word doc in Excel anyway. If you must process
a Word doc from Excel VBA the 'normal' way is to use automation, which means
running an instance of Word from within your Excel VBA, and use that
instance to manipulate the Word file.
--

HTH

RP
(remove nothere from the email address if mailing direct)


"Pinda" wrote in message
...
Hi everyone,

I know how to save a spreadsheet using VBA, but I want to move/copy the
file
after it has been saved, is this possible?

I am creating a system where spreadsheets get dropped into an Inbox folder
(e.g. c:\Inbox) folder and then moved or copied to an Outbox folder (e.g.
c:\Outbox), once they have been edited and saved.

I wish to attached some code to a button that saves the spreadsheet and
then
prompts the user (with a dialogue box), asking if they want to move or
copy
the folder to the outbox, and then close the file down.

I understand that the move may not be possible, as the file is stil open,
but the copy (save as) will be possible.

Any help would be much appreciated.

Also, is it possible to save MS word files through excel VBA code??

Thanks in Advance.

Pinda.

Saving using VBA
 

Bob,

Thanks for that, the save as and kill option sounds good. but will the kill
function work if the file is still open or does it execute when file is
closed?

How would I code something like that, including a dialogue box? My VBA
skills are only basic.

Thanks for your help.

Pinda.

"Bob Phillips" wrote:

Pinda,

You have a number of options.

Personally, I would ask the question before saving and than take the action.
So if they say, Copy, you can do a SaveAs. If they say move, you can do a
Save and a Name, or a Saveas and a Kill on the file in c:\Inbox.

As to saving a word doc, not directly, as Excel saves Excel and various text
formats. Why would you open a word doc in Excel anyway. If you must process
a Word doc from Excel VBA the 'normal' way is to use automation, which means
running an instance of Word from within your Excel VBA, and use that
instance to manipulate the Word file.
--

HTH

RP
(remove nothere from the email address if mailing direct)


"Pinda" wrote in message
...
Hi everyone,

I know how to save a spreadsheet using VBA, but I want to move/copy the
file
after it has been saved, is this possible?

I am creating a system where spreadsheets get dropped into an Inbox folder
(e.g. c:\Inbox) folder and then moved or copied to an Outbox folder (e.g.
c:\Outbox), once they have been edited and saved.

I wish to attached some code to a button that saves the spreadsheet and
then
prompts the user (with a dialogue box), asking if they want to move or
copy
the folder to the outbox, and then close the file down.

I understand that the move may not be possible, as the file is stil open,
but the copy (save as) will be possible.

Any help would be much appreciated.

Also, is it possible to save MS word files through excel VBA code??

Thanks in Advance.

Pinda.

Saving using VBA
 

Hi Pinda,

No, Kill will throw up a permission s error if you have it open, but that is
manageable. Try this

Dim sMess As String
Dim sPath As String
Dim ans

sMess = "Do you want to Move, Copy, or nothing" & vbCrLf & vbCrLf & _
"Press Yes to move, No to Copy, or Cancel to quit"
ans = MsgBox(sMess, vbYesNoCancel, "File Manage")
If ans = vbYes Then
sPath = ActiveWorkbook.FullName
ActiveWorkbook.SaveAs "C:\Outbox\" & ActiveWorkbook.Name
ActiveWorkbook.Close
Kill sPath
ElseIf ans = vbNo Then
ActiveWorkbook.Save
ActiveWorkbook.SaveAs "C:\Outbox\" & ActiveWorkbook.Name
End If

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Pinda" wrote in message
...
Bob,

Thanks for that, the save as and kill option sounds good. but will the
kill
function work if the file is still open or does it execute when file is
closed?

How would I code something like that, including a dialogue box? My VBA
skills are only basic.

Thanks for your help.

Pinda.

"Bob Phillips" wrote:

Pinda,

You have a number of options.

Personally, I would ask the question before saving and than take the
action.
So if they say, Copy, you can do a SaveAs. If they say move, you can do
a
Save and a Name, or a Saveas and a Kill on the file in c:\Inbox.

As to saving a word doc, not directly, as Excel saves Excel and various
text
formats. Why would you open a word doc in Excel anyway. If you must
process
a Word doc from Excel VBA the 'normal' way is to use automation, which
means
running an instance of Word from within your Excel VBA, and use that
instance to manipulate the Word file.
--

HTH

RP
(remove nothere from the email address if mailing direct)


"Pinda" wrote in message
...
Hi everyone,

I know how to save a spreadsheet using VBA, but I want to move/copy
the
file
after it has been saved, is this possible?

I am creating a system where spreadsheets get dropped into an Inbox
folder
(e.g. c:\Inbox) folder and then moved or copied to an Outbox folder
(e.g.
c:\Outbox), once they have been edited and saved.

I wish to attached some code to a button that saves the spreadsheet
and
then
prompts the user (with a dialogue box), asking if they want to move or
copy
the folder to the outbox, and then close the file down.

I understand that the move may not be possible, as the file is stil
open,
but the copy (save as) will be possible.

Any help would be much appreciated.

Also, is it possible to save MS word files through excel VBA code??

Thanks in Advance.

Pinda.