Solved

VBA export sheet

Posted on 2011-03-07
8
369 Views
Last Modified: 2012-05-11
I have an application that loads a tab delimited file into an active sheet.  Works fine.

After suitable alterations I wish to write out the data back to the the same (or other) file.
Works as expected with one unexpected side effect.  It renames the Tab that was saved to the file name of the output file.

I can solve the problem by renaming the Tab back to its original name.

I had hoped that there was a reciprocal export function to the import function.

Excel 2007
0
Comment
Question by:PP11
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 35065519
"Save As" Text(Tab Delimited) doesn't work?

Dave
0
 

Author Comment

by:PP11
ID: 35065740
It does work.  It tried a test where the data was in a tab labeled Expert.  See  Starting.JPG.

I then did a save as=> tab delimited with a file name of Expert-2.JPG

Notice that the tab has been renamed from Export to Export-2

The contents of the files are identical.

Pat
Starting.JPG
Ending.JPG
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35065979
Ok - so I guess I'm confused - would you try once to clarify your question?

:)

Thanks,

dave
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:PP11
ID: 35066012
I am trying to export a sheet in tab delimited format without changing my sheet's Tab name.
0
 
LVL 42

Accepted Solution

by:
dlmille earned 100 total points
ID: 35066121
Ok, the following code should work (you know how to do the saveas function, so change the filename/path where you want to save it).

Put the following with your own modifications in a public Module.  Go to an activesheet you want to make a tab delimited file of, and run this macro.

 
Sub SaveCurrentAsTabDelimited()
Dim myOutputSheet As Worksheet
Dim newWkb As Workbook

Application.DisplayAlerts = False

    ActiveSheet.Copy
    ActiveWorkbook.SaveAs Filename:="outputTabDel.txt", FileFormat:=xlText, CreateBackup:=False
    ActiveWorkbook.Close

Application.DisplayAlerts = True

End Sub

Open in new window


Hope this helps.  I did some googling on it and this seems the best option re: http://www.mrexcel.com/forum/showthread.php?t=146862

Cheers,

Dave
ExportNoTabNameChange-r1.xlsm
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35066125
Sorry - explanation for my last post.  It copys the active sheet tab to a new workbook, then saves that new workbook as tab delimited and closes the file.

:)

Dave
0
 

Author Closing Comment

by:PP11
ID: 35066192
I performed a variety of tests and found that it performed as advertised.

Thank you very much
0
 

Author Comment

by:PP11
ID: 35069495
I think tati will go back to renaming the Tab back to the original
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question