Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VBA export sheet

Posted on 2011-03-07
8
Medium Priority
?
374 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 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's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying 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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
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 …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
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.

916 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