+ Reply to Thread
Results 1 to 16 of 16

import every file in the folder and rename sheet to the imported file name

  1. #1
    Registered User
    Join Date
    08-24-2012
    Location
    Maharashtra, Pune
    MS-Off Ver
    Excel 2007
    Posts
    56

    Question import every file in the folder and rename sheet to the imported file name

    Hello friends

    actually in my office I have a work of converting some prn file to text files, so i want a macro which can import all the prn files in the folder and and save them as a text file with the existing name, so far i found a macro which import multiple files but the problem is that it renames sheet with filename.prn, I dont want to get this .prn

    i possible please give me macro code which import all the prn files in the folder with existing name and save them as text file
    Thanks in advance


    Below is the code which i am current using to import prn file


    Please Login or Register  to view this content.
    Last edited by Cutter; 08-30-2012 at 10:52 AM. Reason: Added code tags

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: import every file in the folder and rename sheet to the imported file name

    eliminate row
    Please Login or Register  to view this content.
    or comment
    Please Login or Register  to view this content.
    If solved remember to mark Thread as solved

  3. #3
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: import every file in the folder and rename sheet to the imported file name

    Hi, vivek_work,

    according to Rule #3 from the Forum Rules please use code tags around your code for better readabilty.

    If you want the sheet to be renamed without the ".prn" use the left part of the string you get without the last 4 characters:
    Please Login or Register  to view this content.
    Ciao,
    Holger

  4. #4
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: import every file in the folder and rename sheet to the imported file name

    @ vivek_work

    Welcome to the forum.

    Please notice that code tags have been added to your post(s). As HaHoBe pointed out the forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at top of the page and read Rule #3.
    Thanks.

  5. #5
    Registered User
    Join Date
    08-24-2012
    Location
    Maharashtra, Pune
    MS-Off Ver
    Excel 2007
    Posts
    56

    Re: import every file in the folder and rename sheet to the imported file name

    Hello guys thanks a lot it helped me to import file without the .prn name now the next step is i want to save them as text file in some folder, like suppose i keep this macro file in Folder1 and run it should save all the text file in the same (Folder1) location, and the name of text file should be same as per the worksheet here is the code i have but it saves all the text file to My Document which I dont want...

    Please Login or Register  to view this content.
    Once again thanks for your help...

  6. #6
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: import every file in the folder and rename sheet to the imported file name

    Hi, vivek_work,

    try it like this:
    Please Login or Register  to view this content.
    Ciao,
    Holger

  7. #7
    Registered User
    Join Date
    08-24-2012
    Location
    Maharashtra, Pune
    MS-Off Ver
    Excel 2007
    Posts
    56

    Re: import every file in the folder and rename sheet to the imported file name

    Hey friend thanks a lot u solved my problem but still i have to specify path to save files can u modify it so that it can save the sheets in the current folder where the macro file is kept coz this file is gona be use by many people on there computer so please modify these code in a way that it should save file in the directory where the macro file is kept

    thanks in advance

  8. #8
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: import every file in the folder and rename sheet to the imported file name

    Hi, vivek_work,

    change this
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.
    Ciao,
    Holger

  9. #9
    Registered User
    Join Date
    08-24-2012
    Location
    Maharashtra, Pune
    MS-Off Ver
    Excel 2007
    Posts
    56

    Re: import every file in the folder and rename sheet to the imported file name

    thanks guys for ur help still my problem is unsolved actually what i want is open multiple prn files in one workbook and name each sheet as per the imported prn file name and then save in sheet in the same location with same name but in text format so i have two piece of code
    1) to import multiple prn files (but problem is it imports it to new workbook)
    Please Login or Register  to view this content.
    2) This code is to save this file to txt format
    Please Login or Register  to view this content.
    can anyone of u please make a macro file with command button to import multiple .prn file and save them as text file with existing name

  10. #10
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: import every file in the folder and rename sheet to the imported file name

    Hi, vivek_work,

    in your code:
    Please Login or Register  to view this content.
    Change that to read:
    Please Login or Register  to view this content.
    if it´s the workbook which holds the code or
    Please Login or Register  to view this content.
    if you want to import into the active workbook (which may not be the workbook with the code).

    Ciao,
    Holger

  11. #11
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: import every file in the folder and rename sheet to the imported file name

    Hi, Hi, vivek_work,,

    see if this code will do what you want (code is untested):

    Please Login or Register  to view this content.
    Ciao,
    Holger

  12. #12
    Registered User
    Join Date
    08-24-2012
    Location
    Maharashtra, Pune
    MS-Off Ver
    Excel 2007
    Posts
    56

    Re: import every file in the folder and rename sheet to the imported file name

    Hi HaHoBe
    thanks the code works very well thank u very much for helping me, and this site is really very helpful member are very helpful and my problem is solved very fast i didn't expected this really very nice and tallented member's are here once again thanks all for helping me and hope in future also i will get help

    Vivek

  13. #13
    Registered User
    Join Date
    08-24-2012
    Location
    Maharashtra, Pune
    MS-Off Ver
    Excel 2007
    Posts
    56

    Re: import every file in the folder and rename sheet to the imported file name

    just one last question the above code is working perfect but is it possible that for selecting .prn file when it opens, open dialog box can it show the current directory instead of showing the windows default path to browse for file

  14. #14
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: import every file in the folder and rename sheet to the imported file name

    Hi, vivek_work,

    the mistery of a solved thread and one more question (maybe you´d better taken away the solved to this thread).

    Instead of
    Please Login or Register  to view this content.
    maybe use
    Please Login or Register  to view this content.
    Ciao,
    Holger

  15. #15
    Registered User
    Join Date
    08-24-2012
    Location
    Maharashtra, Pune
    MS-Off Ver
    Excel 2007
    Posts
    56

    Re: import every file in the folder and rename sheet to the imported file name

    Hi HaHoBe,
    sorry frnd actually my query was solved so i marked it as solved and i thought about the last question at the end so just for my knowledge i asked that question thank u for solving it, really this site is very nice thank u so much i will post any threat if i need any help hope u will always help me

  16. #16
    Registered User
    Join Date
    08-24-2012
    Location
    Maharashtra, Pune
    MS-Off Ver
    Excel 2007
    Posts
    56

    Re: import every file in the folder and rename sheet to the imported file name

    Sorry frnd i tried it now and found that this code is not working
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1