How to open a file with a variable extension in vba excel

Question

I have to made a vba for open a CSV from excel the problem is the files has a format:

constan_name_file . YYY

For example

file1.124514 (Day 1)

file1.144521 (Day 2)

file1.152132 (Another day)

The name file is a constan but the YYY is variable, but the file inside is a CSV. thats possible to open it?

Use Wild Cards in DIR to open the file. Give it a try and if you get stuck simply post the code that you tried and we will take it from there — Siddharth Rout
– Siddharth Rout, Commented Mar 28, 2013 at 15:43

Forward Ed · Accepted Answer · 2020-02-12 15:56:44Z

I've had a problem to keep the formatting when opening a csv-file in Excel 2007. Looked around in excel-forums only to find one possible solution. Change file-extension from csv to txt.

Tried to make a Function as general as possible. Here it is:

Option Explicit

Function ChangeFileExt(filnamn As String, extensionOld, extensionNew)         
Dim oldname, newname as string     

oldname = ThisWorkbook.Path & "\" & filnamn & "." & extensionOld

newname = ThisWorkbook.Path & "\" & filnamn & "." & extensionNew         
Name oldname As newname  

End Function

'...............................................................................

Sub change_extension()     

' Objective: Want to keep csv-format at workbook.open

' Rename file from filename.csv to filename.txt      
' Open txt-file            
' process data          
' Rename back to csv-file if neccesary        


Dim csv, txt As String           

csv = "csv"      
txt = "txt"         

Call ChangeFileExt("file_name_to_change", csv, txt)      '  omit extension in file_name_to_change         

...all kinds of code       

Call ChangeFileName("file_name_to_change", txt, csv)      '  change filename back to original name


End Sub

Potter Rafed · Accepted Answer · 2013-03-28 15:43:14Z

1

Why dont you forcefully open it as a CSV file

Workbooks.Open Filename:=name, Format:=2

Here the 2 specifies that the file is comma delimited http://msdn.microsoft.com/en-us/library/office/ff194819.aspx

answered Mar 28, 2013 at 15:43

Potter Rafed

4904 silver badges14 bronze badges

2 Comments

Jonathan Raul Tapia Lopez Over a year ago

But name =" Full path + name File " its open a file like file1.xlsx or file2.csv the problem is my extension is the time the server made the files and its a variable number

Potter Rafed Over a year ago

right... best thing to do will be to go through each file in the folder (with folder object), get the file and split its name->remove the extention and add your own extention to it. So if a file name was called file214.21Jan13 then use the "dot" as a delimiter and rename the file to file214.csv. Then use normal open to open it with vba.

Jonathan Raul Tapia Lopez · Accepted Answer · 2013-04-08 10:56:46Z

1

Thanks for the use of Wildcards, finally I get the code.

sub GetFiles(direc As String, fich As String) 

    Dim strFileName As Variant

    strFileName = dir(direc & "\" & fich & ".******")

       If Len(strFileName) > 0 Then
        'open
       end if

End sub

answered Apr 8, 2013 at 10:56

Jonathan Raul Tapia Lopez

1,41310 gold badges26 silver badges31 bronze badges

Comments

Our Man in Bananas · Accepted Answer · 2015-03-24 11:46:39Z

1

Yes, try the below code:

dim sFileName as string
dim sExtension as string

sExtension="YYY"
sfilename=constan_name_file & "." & sExtension

workbooks.open sfilename, Format:=2

edited Mar 24, 2015 at 11:46

user142162

answered Mar 28, 2013 at 15:44

Our Man in Bananas

6,00525 gold badges104 silver badges158 bronze badges

4 Comments

Jonathan Raul Tapia Lopez Over a year ago

The YYY changes every day file1.114525 next day file1.145245 another day file.45145

Our Man in Bananas Over a year ago

so that's how you would assign thesExtension variable.... for example, you could pass in the extension to use, or, as Siddharth Rout suggests, use DIR function with wildcards to get the names of the files

Jonathan Raul Tapia Lopez Over a year ago

but I never going to know What is gonna be that variable value-

Our Man in Bananas Over a year ago

ok,. you need to use FileSystemObject to get the names of all the files using wildcards, then you can open the correct one.

Collectives™ on Stack Overflow

How to open a file with a variable extension in vba excel

4 Answers 4

Tried to make a Function as general as possible. Here it is:

Comments

2 Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Tried to make a Function as general as possible. Here it is:

Comments

2 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related