PDA

View Full Version : Solved: How to write imported file's name in a cell?



Conrad
09-07-2010, 06:01 AM
Hello,

I created a macro to import a .txt file and I want this macro also to put the imported file's name in a cell, as reference. How can I do it?

Thanks in advance!

Conrad

Simon Lloyd
09-07-2010, 07:53 AM
errrm, posting the macro would be a good start!
Sheets("Sheet1").Range("A1").Value = IMPORTED FILENAME

Conrad
09-08-2010, 01:18 AM
Oh, sorry, you are right, I need to give much more details :yes
So, here is a sample of my vba code:


Sub openfiletxt()
Dim fileopen As Variant
Dim name As String, importfile, transfersheet
Dim i As Integer, li As Integer
importfile = "Imported_file"
transfer = "Transfer_sheet"
fileopen = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If fileopen <> False Then
Workbooks.OpenText Filename:=fileopen, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False
End If
Range("B1").Value = fileopen

As you see, I already found the solution (last code line) and now I'm trying to find out how to add a macro to save the excel file under that name ("fileopen").

Thank you!

Conrad

Bob Phillips
09-08-2010, 01:37 AM
Activeworkbook.SaveAs fileopen

Conrad
09-08-2010, 04:07 AM
Thank you, xld!

I have introduced the following code in my macro:

ActiveWorkbook.SaveAs Filename:=fileopen, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

However, I receive the following error message, which is caused by this new code line:
"Run-time error '1004'
The file could not be accessed. Try one of the following:
..."

It should just save the new created file with imported .txt as .xls, using the imported .txt file name (if possible, also cutting out the .txt part of the name).

How can I solve this?

Conrad

Simon Lloyd
09-08-2010, 04:50 AM
Thats because the variable fileopen will be an entire location and filename i.e
"C:\Documents\MyFolder\MyFile.txt"

Conrad
09-08-2010, 05:19 AM
Ok, and how can I use only the file name from that variable (without the location informations)? Would it be possible to get only the file name, considering that the path of the file could be sometimes longer, sometimes shorter?

Bob Phillips
09-08-2010, 05:33 AM
See if this works



ActiveWorkbook.SaveAs Filename:=Replace(fileopen, ".txt", ".xls"), _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

Conrad
09-09-2010, 03:38 AM
Thank you xld, but would it also be possible to "cut" the path part ("C:\Documents\MyFolder\" as in Simon_Lloyd's example) in order to keep only the name of the imported file?

Bob Phillips
09-09-2010, 04:29 AM
fileopen = Mid$(fileopen, InStrRev(fileopen, "\") + 1, InStrRev(fileopen, ".") - InStrRev(fileopen, "\") - 1)
ActiveWorkbook.SaveAs Filename:=fileopen, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False