Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBA export sheet

Posted on 2011-03-07
8
Medium Priority
?
372 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 400 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

715 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