CODEWITHSUNDEEP
excelvbareplacefile-extension
Brian
Brian

Reputation: 1138

In vba How to change extension when saving file, don't know exact filename

My issue is this.

I read in a series of filenames using a wildcard, so that the end of the filename is unknown, and the extension is either .xls or .xlsx. So, the wildcard is something like :

beginningOfFilename_*.xls*

I then want to take each file, after I have manipulated it, and save it with the same name, but as a .csv (comma seperated value file). In vba for excel, can I just specify the format and it will take care of the extension, or do I have to somehow pull off the( unknown) extension, and append .csv

If the second case is neccessary how would you approach this problem, I don't know where to start, since part of the filename is unknown, and I am not sure how to manipulate strings in vba.

I'm a VBA beginner.

Any help will be appreciated, thanks.

Upvotes: 0

Views: 9813

Answers (3)

Vatsa
Vatsa

Reputation: 48

Split(sFile, ".")(0) & ".csv"

where sFile is the filename

Upvotes: 2

Fionnuala
Fionnuala

Reputation: 91356

The line you want is :

Mid(sFile, 1, InStrRev(sFile, ".")) & "csv"

Where sFile is the file name with any extension.

Upvotes: 6

Bruno Leite
Bruno Leite

Reputation: 1477

To get a path and name of your file without extension use.

Dim StrFileName as string

StrFileName= split(ThisWorkbook.fullName,".xls")(0)

Now save your Csv using StrFileName content.

[]´s

Upvotes: 1

Related Questions