Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Macro output
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Macro output
Resolved · Low Priority · Version 2003
Reply Mon 29 Jun 2009, 16:55Delegate Ann said...
Ann has attended:
Excel VBA Intro Intermediate course
Excel VBA Intro Intermediate course
Macro output
I want to create a macro that creates a text file based on the information in the excel file.
For upcoming training course dates see: Pricing & availability
Reply Tue 30 Jun 2009, 11:13Delegate Tony said...
RE: Macro output
Ann, please see the code below.
This was created by the macro recorder while I simply saved my Excel workbook as type txt ( tab delimited ) , by choosing .txt rather than the default .xls in the drop-down box on the Save As dialog.
This macro saves the active workbook to a particular location ( C:\Documents and Settings\txmoore\Desktop) and with a particular filename ( MyTextFile.txt ), both of which are hard-coded.
If you need your macro to always save the active workbook to the same place, with the same name, then simply replace my details with your own.
For a full list of the options available, search excel vba help for " SaveAs Method"
If however, the workbook to be saved, the location, or filename need to vary, then define these as variables instead, and use the variable names within the saveas arguments.
If this is the case, then lalso ook within help at the GetSaveAsFileName method, which can be called within your code to display the Save as dialog box, and allow you to browse to your desired location, and choose your filename and type.
The GetSaveAsFileName method does not actually save anything, but simply obtains the filename / location details that you have chosen which can then assigned to variables that are then used by a seperate SaveAs method to actually save the file.
Regards,
Tony Moore
Sub SaveAsTextFile()
'
' SaveAsTextFile Macro
' Macro recorded 30/06/2009 by Tony Moore x8246
'
'
' ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\txmoore\Desktop\MyTextFile.txt", _
FileFormat:=xlText, CreateBackup:=False
End Sub
|