Search : in
By :

Rename the sheet to the file name with date

Last answer on Jul 27, 2009 6:28:13 am BST Diamondust, on Jul 2, 2009 9:18:09 am BST 
 Report this message to moderators

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

Configuration: Windows Vista Internet Explorer 7.0

Best answers for « rename the sheet to the file name with date » in :
Rename file with datetime appended to filenam Show
Renaming multiple files in batch Show Renaming multiple files in batch Native features Using a third-party program Renaming a large number of files can quickly become tedious. Fortunately, there are tools to automate this task in many cases: Native features On Windows...
How to merge an excel file to database? (Solved) Show
Batch Script - Move files to \%date%\%time%\ ShowBatch Script - Move files to \%date%\%time%\ Issue Solution Note Issue I have been trying to make a simple batch script to create database backups and move then into a folder named by date, and a sub folder by time. This is what I...
Writing in batch in text file ShowWriting in batch in text file To write in a file text, you just have to use a redirect: echo text > output_file.txt To write in an existing file: echo " Write at the end of the file ">> output_file.txt
[Shell] Viewing a binary file: Terminal illegible Show[Shell] Viewing a binary file: Terminal illegible When you unintentionally display contents of a binary file (e.g. cat /bin/mount | more), it may happens that the terminal displays characters which are not printable, causing the display of...
Download Instant File Name Search ShowAlthough the research already exists on Windows, sometimes you need more specific tools for specific tasks. Instant File Name Search Instantly search files by name on your computer or network. Advantage It has a simple and convenient interface...
Download PFrank ShowWhen you want to rename or make corrections on file names, this represents an exciting and exhausting task. Pfrank offers almost everything for you. You can rename the file names and folders in Windows. You can easily organize your music files,...
Download File Renamer Basic ShowDo not try any more to rename manually all your files in mass after data transfer. File Renamer BASIC is a powerful utility to rename folders, files, films, images, etc… In fact, you can easily rename folders even the entire subfolders of image or...
Linux - The shell ShowIntroduction to the shell The command interpreter is the interface between the user and the operating system, hence the name "shell". The shell therefore acts as an intermediary between the operating system and the user thanks to command lines...
MS DOS Files and directories ShowFiles in MS DOS In a computers data is stored in files. When you run a program, MS-DOS processes the data stored in the file and passes it to the system. In MS-DOS a file can be any size, however the file name is more restricted, it can only have...
Worksheet - Cells ShowThe Concept of a Cell A "cell" is the intersection between a line (horizontal) and a column (vertical) on a worksheet. Thus, the name of the line combined with the name of the column gives the cell's coordinates (the term address is sometimes also...
Ask your question Reply

1

venkat1926, on Jul 3, 2009 1:07:15 am BST
  • +2

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

   
Reply to venkat1926

2

Diamondust, on Jul 3, 2009 8:18:40 am BST

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)

   
Reply to Diamondust

3

venkat1926, on Jul 3, 2009 12:25:08 pm BST

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.

   
Reply to venkat1926

5

Diamondust, on Jul 9, 2009 12:42:54 am BST

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.

   
Reply to Diamondust

4

Diamondust, on Jul 6, 2009 1:43:13 am BST

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.

   
Reply to Diamondust

6

venkat1926, on Jul 9, 2009 1:09:53 am BST

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.

   
Reply to venkat1926

7

 Diamondust, on Jul 27, 2009 6:28:13 am BST

Hi,

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

   
Reply to Diamondust
Ask your question Reply
They need your help