+ Reply to Thread
Results 1 to 10 of 10

Save As Text (Tab Delimited)

  1. #1
    Registered User
    Join Date
    04-06-2018
    Location
    DFW, TX
    MS-Off Ver
    2013
    Posts
    5

    Save As Text (Tab Delimited)

    I have inherited a workbook that is in need of some maintenance. There is one area where I'm a little stumped.

    At the end of the macro I am working on, I need it to save a single sheet as a Text (Tab Delimited) file.

    The macro that I inherited contains this:

    Please Login or Register  to view this content.
    That pops up a "Save As" dialogue box, but forces the file to be saved as a .xlsx. Since I now want it to be a .txt file, I originally modified it to this:

    Please Login or Register  to view this content.
    The .txt that it saves it as, however, is really screwy. Weird characters abound and it isn't formatted the same as if I were to manually open the workbook, click Save As, and choose Text (Tab Delimited) and save the file myself. I don't know why that is, but it appears to be trying to save a whole workbook instead of a sheet?

    To see if I could get an output that at least worked, I did this code:

    Please Login or Register  to view this content.
    The file that it output with that code is perfect. Formatted correctly, no strange characters, etc.

    The problem is that it can't be used in a workbook that will be accessed by multiple users since the file path is different for everyone.

    What I'm graciously requesting help with is to have a prompt like the first code provides that allows the user to select where the file will be saved, but have it save in the xlText format that I utilized in the third code.

    This would let every user put the file where they want and ensure that the saved file is in the proper format.

    I hope this makes sense.

    I thank you for your consideration of my dilemma and appreciate all assistance!

    See post #10 regarding the fact that this is on Mr. Excel forums (unsolved or unaddressed) and no double-work has been done on this issue.
    Last edited by bvbull200; 04-09-2018 at 05:17 PM.

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,623

    Re: Save As Text (Tab Delimited)

    This will likely need FreeFile method to write to file.

    Try the method outlined in the link.
    http://dailydoseofexcel.com/archives...aveasfilename/

    If you have issues, post here and I'll help.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,623

    Re: Save As Text (Tab Delimited)

    On second thought, you can try this.

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    04-06-2018
    Location
    DFW, TX
    MS-Off Ver
    2013
    Posts
    5

    Re: Save As Text (Tab Delimited)

    Quote Originally Posted by CK76 View Post
    On second thought, you can try this.

    Please Login or Register  to view this content.
    I think this does the trick for me! Thanks a bunch for that. The file looks good, I'm just going to have to run it through the tool that these files have to get uploaded to and make sure everything go smoothly.

    An extension on that question, though. Is it possible to have the Filename default to a predetermined format? I would like to have the default name be "[Value of Cell D2] - [Date] - [Time]". Doable?

    I really appreciate the assistance.

  5. #5
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,623

    Re: Save As Text (Tab Delimited)

    Yes. You could. I would use FileDialog to pick folder in that case, instead of GetSaveAsFilename.

    Something like below, assuming sheet with [D2] is active when the code runs.
    Format() is used to change date/time format to have only numeric (i.e. remove illegal characters in file name).
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    04-06-2018
    Location
    DFW, TX
    MS-Off Ver
    2013
    Posts
    5

    Re: Save As Text (Tab Delimited)

    Quote Originally Posted by CK76 View Post
    Yes. You could. I would use FileDialog to pick folder in that case, instead of GetSaveAsFilename.

    Something like below, assuming sheet with [D2] is active when the code runs.
    Format() is used to change date/time format to have only numeric (i.e. remove illegal characters in file name).
    Please Login or Register  to view this content.

    I am tantalizingly close, but I keep running in to one little bug. Using both the code you provided and the parsed down version that I am now using, I get the desired result the first time, but if the macro runs a second time, the whole workbook hangs up and I can't terminate the macro, close the program, or anything. I get relegated to ending the task via the task manager. Any idea why that is?

    The 1st code I provided in post #1 can be run over and over with no hangup. I have replaced it with the following:
    Please Login or Register  to view this content.
    Now I am getting hung up if it is run a second time.

  7. #7
    Registered User
    Join Date
    04-06-2018
    Location
    DFW, TX
    MS-Off Ver
    2013
    Posts
    5

    Re: Save As Text (Tab Delimited)

    Eh, might be something with my machine. The most repeatable version of that bug was if I did not select a folder (hit cancel instead), then tried to run the macro again. It kept hanging up.

    I've rebooted the computer and the issue seems to have gone away. Looks like I'm all set.

  8. #8
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,623

    Re: Save As Text (Tab Delimited)

    Glad you got it solved

    If further issues, let me know.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,104

    Re: Save As Text (Tab Delimited)

    Rule 08: Cross-posting Without Telling Us

    Your post does not comply with Rule 8 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

    Post a link to any other forums where you have asked the same question. If you have fewer than 10 posts here, you will not be able to post a link, but you must still tell us where else you have asked the question.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

    No further help to be offered, please, until the OP has complied with this request.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  10. #10
    Registered User
    Join Date
    04-06-2018
    Location
    DFW, TX
    MS-Off Ver
    2013
    Posts
    5

    Re: Save As Text (Tab Delimited)



    Posted on Mr. Excel right before I had a coworker recommend this forum instead. I can't post links, so not sure how to help you out, there. I guess search for a thread that says "Save As Text (Delimited)" or something? I don't know. No help from the original forum, so there is no double-work being done here or anything.

    Big thanks to CK76, though, for the willingness to help so quickly and for creating a very helpful solution. I'm still tweaking to meet some new 'needs' of the project, but I'm on the right path with it, now!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Save sheet as tab delimited text file issue
    By captbluefin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-15-2017, 01:42 PM
  2. code to save as tab delimited text
    By kevinu in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-27-2017, 03:29 PM
  3. VBA to Save a Single Sheet as a Tab Delimited Text File
    By mattmc419 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-05-2014, 03:34 PM
  4. save as pipe delimited text file.
    By aryam in forum Excel General
    Replies: 2
    Last Post: 04-13-2011, 11:14 AM
  5. Save Excel file as Tab-delimited text without quotes
    By markjh in forum Excel General
    Replies: 6
    Last Post: 10-06-2010, 01:52 AM
  6. Save as a pipe delimited text file
    By Jetheat in forum Excel General
    Replies: 5
    Last Post: 07-01-2010, 08:07 AM
  7. [SOLVED] Save as Text (tab delimited) problem
    By Prema in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-19-2005, 10:05 AM

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