Macro

[Guest]

Hi, I have a bunch of documents that are .csv in excel. WHat I am doing is
opening each, deleteing a bunch of colums, autowidth and then saving the file
in another folder as an .xls file. I have everything working but the
filename. I cannot figure out how to do the following:
1, save the file as an excel file I.E. the filename is test.csv, I want the
macro to be able to save it as test.xls And when I open the next file called
test2.csv I want it to save as test2.xls. Right now it asks me if I want to
overwrite whats there because it is trying to save everything as test.xls.

Hope I made this clear (thats not one of my strong points). Thanks in
advance.

 

[Bernie Deitrick]

Something along the lines of:

ActiveWorkbook.SaveAs Replace(ActiveWorkbook.Name, ".csv", ".xls"), xlNormal

HTH,
Bernie
MS Excel MVP

 

[MartinShort]

This should do it for you. You will need to modify the pathname to your
specifications.


Code:
--------------------
Sub SaveNextFileId()
Dim id As Integer
Dim path As String
Dim filename As String
id = 1
path = "C:\Test\" 'will need to be set to your own specifications.

Do
filename = path & "test" & id & ".xls"
id = id + 1
Loop Until Dir(filename) = "" ' i.e. until the file doesn't exist yet!

ActiveWorkbook.SaveAs filename:=filename, FileFormat:=xlNormal
End Sub

 

[Puppet_Sock]

ib_redbeard wrote:
[how to in VBA]

Two other folks have given answers to the specific question.
In the "teach him to fish" plan, the Macro recorder is your friend.
If you record saving something the way you need it saved, then
look at the VBA code, it should give you some very strong hints
about what part you need changed. If it's a step you can do
manually then the macro recorder will nearly always give you
such hints.
Socks

 

[dbahooker]

this type of activity is MUCH easier with Access or some other database
system.

keep your data in a database; and use excel as a doorstop

 

[Puppet_Sock]

this type of activity is MUCH easier with Access or some other database
system.

keep your data in a database; and use excel as a doorstop

It is polite to quote enough of what you are responding to in order
to be able to have some context. Not everybody is reading this on a
system that shows the old messages at the same time. If you are
doing this through google, then click the Options link first, then the
Reply link that is provided.

"This type of activity" was converting CSV files to XLS format. No,
that
is not easier in Access.

Keeping data in Access makes sense in some situations. Not in all.
Some times what you want is a big old flat table. Some times you
want a feature of a spread sheet rather than a database.
Socks

 

[Guest]

Thanks guys, it really helped a lot!