Solved: Help with debugging a script - save all pages in Workbook as .txt file

[Chipley]

Hi,

I just joined this community today, so I'm a first time poster.

I have a question which I hope will be an easy answer. I am working on a script that will save all worksheets in a workbook to a.txt file. I have been receiving some help on the following script; however, there is an error, and I don't have enough experience with VBA to debug. When I run this script, I get an error stating "Compile error - User defined type not defined", which in turn points to 'd As DataObject'. Could someone possibly lend a helping hand and tell me where my n000b logic has gone wrong

Sub SaveAsTextFile()
Dim s As Worksheet
Dim d As DataObject
Set d = New DataObject
Open "c:\temp\mytest.txt" For Append As 1
For Each s In ThisWorkbook.Sheets
s.UsedRange.Copy
d.GetFromClipboard
Print #1, vbNewLine & s.Name & vbNewLine & String(Len(s.Name), "=")
Print #1, d.GetText
Next
Close 1
End Sub

Much appreciation for any help on a Sunday.

Cheers,
~Chipley

[Bob Phillips]

DataObject is a member of the MSForms type library. You should have a reference to that, but check it. In the VBIDE goto Tool>References adn see if the Microsoft Forms type library is checked.

[Chipley]

ty for the reply xld.

interesting, I actually can't find 'Microsoft Forms type library' in my list under Tools>References. The 4 items that are checked in my 'References' list are:


Visual Basic For Applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object LibraryThe item you mentioned is not in my list, but I'll start 'googling' about it now...

[mdmackillop]

Use Microsoft Forms 2.0 Object Library

[Norie]

Try this.

1 Add a userform.

2 Remove it.

[Chipley]

Brilliant! TY for your help xld, mdmackillop, & Norie.

Since I mentioned that I couldn't find the Microsoft Forum Library, I took your suggestion Norie, added a UserForm, looked in the Reference Panel again, and sure enough, I found the Microsoft Forms 2.0 Object Library, ticked it, and now my export function is working perfectly.


Cheers to all 3 of you :beerchug:

Much appreciation,
~Chipley