Solved

VBA export sheet

Posted on 2011-03-07
8
364 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
  • 4
  • 4
8 Comments
 
LVL 41

Expert Comment

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

Dave
0
 

Author Comment

by:PP11
Comment Utility
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 41

Expert Comment

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

:)

Thanks,

dave
0
 

Author Comment

by:PP11
Comment Utility
I am trying to export a sheet in tab delimited format without changing my sheet's Tab name.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 41

Accepted Solution

by:
dlmille earned 100 total points
Comment Utility
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 41

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
I performed a variety of tests and found that it performed as advertised.

Thank you very much
0
 

Author Comment

by:PP11
Comment Utility
I think tati will go back to renaming the Tab back to the original
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now