Solved

VBA export sheet

Posted on 2011-03-07
8
365 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
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 41

Expert Comment

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

:)

Thanks,

dave
0
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 41

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 41

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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 code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

920 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

17 Experts available now in Live!

Get 1:1 Help Now