rename the sheet to the file name with dateClosed

Question

Hello,

I need to import text file to excel and rename the sheet to the name of the textfile using macro. I have the code for importing the text file but have no idea how to rename it to the filename. for example my file name is  test.txt , how can i change the sheet1 to this test.txt and get the current time when importing. Below is my code, please help me to edit it to show the filename in the sheet and display the time in the sheet as well. thank you very much

With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:%username%Desktop ", _
        Destination:=Range("G1"))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = True
        .TextFilePlatform = xlWindows
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .Refresh BackgroundQuery:=False

    End With

venkat1926 · Accepted Answer

KEEP THE ORIGINAL FILE SAFELY SOMEWHERE SO THAT IT CAN BE RETRIEVED IF  THERE IS PROBLEM

TRY this maodified macro 

dim file as string


CODE
file="text"
With ActiveSheet.QueryTables.Add(Connection:= _
 & file & ";C:%username%Desktop ", _
Destination:=Range("G1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = True
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.Refresh BackgroundQuery:=False

activesheet.name=file & format(now,"hhmmss")
CODE ENDS

Diamondust · Answer

Hi,

Thanks alot for your help, now i can have time and date in the sheet but i a problem. The code that you modified has an error.

With ActiveSheet.QueryTables.Add(Connection:= _
 & file & ";C:%username%Desktop ", _
Destination:=Range("G1"))

It show syntax error on this line of the code. I try to change it to

With ActiveSheet.QueryTables.Add(Connection:= _
 file & ";C:%username%Desktop ", _
Destination:=Range("G1"))

I just took out one of the "&" and the code can work but the sheet name will be change to text010203
What im looking for is to be able to change to the file name which i select.

for example if i select my file   output.txt    the sheet1 will change to output.txt010203

If i change the code wrongly and which is why i got text010203 instead of output.txt010203 please correct me.
If the code is meant to give this result (text010203) please show me a way to change my sheet1 to my file name(output.txt010203). Thank you.

Im very sorry if i did not made my question clear. (My English is bad)

venkat1926 · Answer

You have given the url of the webpage as text. I thought that is the full url 

what is the full url address form which you download data.

Diamondust · Answer

Hi,

The full url is "C:\Users\jqup\Desktop\update list" where I can choose the .txt from this folder but I want to use this script on any computer which is why I use "C:%username%Desktop" so that they can select the file on their desktop when i send to them.
 
I think my code is wrong if you mean text is my url.

Thanks again.

venkat1926 · Answer

your url is 

"C:\Users\jqup\Desktop\update list"


now suppose  the user name is "abcd" then I suppose your url will be 
 
c:\abcd\jqup\desktop\update list

now here abcd is the variable will change from computer to computer 


now your try this 

dim text as string
dim user as string
user=inputboc("type the name of the user for e.g. abcd")

text="c:\" & user & "\jqup\desktop\update list"

now you an use  this in your code the firstlines are 

With ActiveSheet.QueryTables.Add(Connection:= _
text, _ 

perhaps still there is some bug. try and post the problem 
greetings.

Rename the sheet to the file name with date

5 responses

Similar discussions

Subscribe To Our Newsletter!