Rename the sheet to the file name with date [Closed]

Report
-
 Diamondust -
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

5 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
796
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
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

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)
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
796
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.
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.
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.
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
796
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.
Hi,

My problem is sloved. Thanks alot for your help. How do i close this thread?

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!