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

  • Thread starter Thread starter William Prendergast
  • Start date Start date
W

William Prendergast

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
 
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]
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top