Using 'SAVE AS' within macro with mutliple files and keeping default savename
 

I've seen this question asked several different ways but not in this
way.
If so please forgive me if this is a duplicate. I know this is a
really easy question, based on some of the stuff that already seen in
this form, but I don't not work with Excel and am just a lowly Unix
Administrator that needs to
convert alot of excel files into html.

I tried recording a macro that takes several already opened
speadsheets (the names change constantly).

s0adcr4p9.1228
s0adcr4p9.1229
s0adcr5p9.1228
s0adcr5p9.1229

and saves them as a separate html file. Basically once they
are open, do a File/Save As Web Page, then a File Close, which goes
to the
next doc and the process repeats the ideal task for a macro.

The macro I created works fine however it is not using the default
savename but instead it wants to use what was used in the macro and
I receive an error that the file already exists.

How can I change the macro to read the name of the file
thats being opened and then save it as that name with a ".htm" on the
end?

I know what I want to do.. I am just not familiar enough with excel
to do it.

Here is the macro as it was generated:

Sub NMON()
'
'
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\sswjp1\My
Documents\NMON\ARCH\s0adcr4p9.1228.htm", _
FileFormat:=xlHtml,
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
End Sub



Somehow I need to replace the s0adcr4p9.1228 with the default save as
name!


Any and all help is appreciated..

Thanks

-Bill

Using 'SAVE AS' within macro with mutliple files and keeping default savename
 

Hi William,

You can use the Name property of the ActiveWorkbook to get what you want:

ActiveWorkbook.SaveAs Filename:="C:\Documents and " _
& "Settings\sswjp1\My Documents\NMON\ARCH\" & _
ActiveWorkbook.Name & ".htm", _
FileFormat:=xlHtml, ReadOnlyRecommended:=False, _
CreateBackup:=False

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


William Prendergast wrote:
I've seen this question asked several different ways but not in this
way.
If so please forgive me if this is a duplicate. I know this is a
really easy question, based on some of the stuff that already seen in
this form, but I don't not work with Excel and am just a lowly Unix
Administrator that needs to
convert alot of excel files into html.

I tried recording a macro that takes several already opened
speadsheets (the names change constantly).

s0adcr4p9.1228
s0adcr4p9.1229
s0adcr5p9.1228
s0adcr5p9.1229

and saves them as a separate html file. Basically once they
are open, do a File/Save As Web Page, then a File Close, which goes
to the
next doc and the process repeats the ideal task for a macro.

The macro I created works fine however it is not using the default
savename but instead it wants to use what was used in the macro and
I receive an error that the file already exists.

How can I change the macro to read the name of the file
thats being opened and then save it as that name with a ".htm" on the
end?

I know what I want to do.. I am just not familiar enough with excel
to do it.

Here is the macro as it was generated:

Sub NMON()
'
'
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\sswjp1\My
Documents\NMON\ARCH\s0adcr4p9.1228.htm", _
FileFormat:=xlHtml,
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
End Sub



Somehow I need to replace the s0adcr4p9.1228 with the default save as
name!


Any and all help is appreciated..

Thanks

-Bill

Using 'SAVE AS' within macro with mutliple files and keeping default savename
 

Jake,

Thanks for your helpful response.

The only other thing I would need to do is strip out the ".xls"
from the filename and then add the ActiveWorkbook.Close to get it to
close each open doc.

I need the name to be s0adcr5p9.1229_files (file folder)
s0adcr5p9.1229 (html)




"Jake Marx" wrote in message ...
Hi William,

You can use the Name property of the ActiveWorkbook to get what you want:

ActiveWorkbook.SaveAs Filename:="C:\Documents and " _
& "Settings\sswjp1\My Documents\NMON\ARCH\" & _
ActiveWorkbook.Name & ".htm", _
FileFormat:=xlHtml, ReadOnlyRecommended:=False, _
CreateBackup:=False

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


William Prendergast wrote:
I've seen this question asked several different ways but not in this
way.
If so please forgive me if this is a duplicate. I know this is a
really easy question, based on some of the stuff that already seen in
this form, but I don't not work with Excel and am just a lowly Unix
Administrator that needs to
convert alot of excel files into html.

I tried recording a macro that takes several already opened
speadsheets (the names change constantly).

s0adcr4p9.1228
s0adcr4p9.1229
s0adcr5p9.1228
s0adcr5p9.1229

and saves them as a separate html file. Basically once they
are open, do a File/Save As Web Page, then a File Close, which goes
to the
next doc and the process repeats the ideal task for a macro.

The macro I created works fine however it is not using the default
savename but instead it wants to use what was used in the macro and
I receive an error that the file already exists.

How can I change the macro to read the name of the file
thats being opened and then save it as that name with a ".htm" on the
end?

I know what I want to do.. I am just not familiar enough with excel
to do it.

Here is the macro as it was generated:

Sub NMON()
'
'
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\sswjp1\My
Documents\NMON\ARCH\s0adcr4p9.1228.htm", _
FileFormat:=xlHtml,
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
End Sub



Somehow I need to replace the s0adcr4p9.1228 with the default save as
name!


Any and all help is appreciated..

Thanks

-Bill

Using 'SAVE AS' within macro with mutliple files and keeping default savename
 

Hi William,

There are a few ways to strip out the .xls:

Left$(ActiveWorkbook.Name, Len(ActiveWorkbook.Name)-4)

or

Replace$(ActiveWorkbook.Name, ".xls", "")

Is that what you are looking for?

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


William Prendergast wrote:
Jake,

Thanks for your helpful response.

The only other thing I would need to do is strip out the ".xls"
from the filename and then add the ActiveWorkbook.Close to get it to
close each open doc.

I need the name to be s0adcr5p9.1229_files (file folder)
s0adcr5p9.1229 (html)




"Jake Marx" wrote in message
...
Hi William,

You can use the Name property of the ActiveWorkbook to get what you
want:

ActiveWorkbook.SaveAs Filename:="C:\Documents and " _
& "Settings\sswjp1\My Documents\NMON\ARCH\" & _
ActiveWorkbook.Name & ".htm", _
FileFormat:=xlHtml, ReadOnlyRecommended:=False, _
CreateBackup:=False

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


William Prendergast wrote:
I've seen this question asked several different ways but not in this
way.
If so please forgive me if this is a duplicate. I know this is a
really easy question, based on some of the stuff that already seen
in this form, but I don't not work with Excel and am just a lowly
Unix Administrator that needs to
convert alot of excel files into html.

I tried recording a macro that takes several already opened
speadsheets (the names change constantly).

s0adcr4p9.1228
s0adcr4p9.1229
s0adcr5p9.1228
s0adcr5p9.1229

and saves them as a separate html file. Basically once they
are open, do a File/Save As Web Page, then a File Close, which goes
to the
next doc and the process repeats the ideal task for a macro.

The macro I created works fine however it is not using the default
savename but instead it wants to use what was used in the macro
and I receive an error that the file already exists.

How can I change the macro to read the name of the file
thats being opened and then save it as that name with a ".htm" on
the end?

I know what I want to do.. I am just not familiar enough with excel
to do it.

Here is the macro as it was generated:

Sub NMON()
'
'
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\sswjp1\My
Documents\NMON\ARCH\s0adcr4p9.1228.htm", _
FileFormat:=xlHtml,
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
End Sub



Somehow I need to replace the s0adcr4p9.1228 with the default save
as name!


Any and all help is appreciated..

Thanks

-Bill

Using 'SAVE AS' within macro with mutliple files and keeping default savename
 

Jake

Thanks again.. that worked well...

I just have to fix one last runtime variable with my looping structure
and should be good to go..

Here is what I have thus far.. I've tried the loop several different
ways but I can't get it to leave the loop and terminate.

sheetcount = ActiveWorkbook.Sheets.Count

Do While ActiveWorkbook.Name < ""

For j = 0 To sheetcount - 1 Step 1

filesavename = ActiveWorkbook.Name
filesavename = Replace$(ActiveWorkbook.Name, ".xls", "")

If filesavename < "False" Then
ActiveWorkbook.SaveAs Filename:=filesavename,
FileFormat:=xlHtml, _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
End If

Next

Loop

End Sub


"Jake Marx" wrote in message ...
Hi William,

There are a few ways to strip out the .xls:

Left$(ActiveWorkbook.Name, Len(ActiveWorkbook.Name)-4)

or

Replace$(ActiveWorkbook.Name, ".xls", "")

Is that what you are looking for?

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


William Prendergast wrote:
Jake,

Thanks for your helpful response.

The only other thing I would need to do is strip out the ".xls"
from the filename and then add the ActiveWorkbook.Close to get it to
close each open doc.

I need the name to be s0adcr5p9.1229_files (file folder)
s0adcr5p9.1229 (html)




"Jake Marx" wrote in message
...
Hi William,

You can use the Name property of the ActiveWorkbook to get what you
want:

ActiveWorkbook.SaveAs Filename:="C:\Documents and " _
& "Settings\sswjp1\My Documents\NMON\ARCH\" & _
ActiveWorkbook.Name & ".htm", _
FileFormat:=xlHtml, ReadOnlyRecommended:=False, _
CreateBackup:=False

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


William Prendergast wrote:
I've seen this question asked several different ways but not in this
way.
If so please forgive me if this is a duplicate. I know this is a
really easy question, based on some of the stuff that already seen
in this form, but I don't not work with Excel and am just a lowly
Unix Administrator that needs to
convert alot of excel files into html.

I tried recording a macro that takes several already opened
speadsheets (the names change constantly).

s0adcr4p9.1228
s0adcr4p9.1229
s0adcr5p9.1228
s0adcr5p9.1229

and saves them as a separate html file. Basically once they
are open, do a File/Save As Web Page, then a File Close, which goes
to the
next doc and the process repeats the ideal task for a macro.

The macro I created works fine however it is not using the default
savename but instead it wants to use what was used in the macro
and I receive an error that the file already exists.

How can I change the macro to read the name of the file
thats being opened and then save it as that name with a ".htm" on
the end?

I know what I want to do.. I am just not familiar enough with excel
to do it.

Here is the macro as it was generated:

Sub NMON()
'
'
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\sswjp1\My
Documents\NMON\ARCH\s0adcr4p9.1228.htm", _
FileFormat:=xlHtml,
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
End Sub



Somehow I need to replace the s0adcr4p9.1228 with the default save
as name!


Any and all help is appreciated..

Thanks

-Bill