Need to convert old Excel macro to E2010

bulrush

Registered User.
Local time
Today, 08:02
Joined
Sep 1, 2009
Messages
209
This is a macro which saves the current spreadsheet tab as a tab delimited file with an extension of ".dat". It does not work in 2010. How can I convert it to work in 2010?
Code:
Dim wb As Workbook
Dim s As String

Set wb = Application.ActiveWorkbook

    s = wb.FullName ' <== Error is here
    s = Replace(s, ".xls", ".dat")
    ActiveWorkbook.SaveAs Filename:=s, FileFormat:=xlText, CreateBackup:=False
    MsgBox "Saved tab-delim file as " & s
Thanks.
 
Last edited:
2007 & 2010 extensions are 4 digits long xlsx therefore you dat file would endup as .datx

change your replace to include xlsx,dat
 
Nope. The open worksheet is actually .xls. I must have edited my question after you posted. The error I get is in the below line: "Object not set" I think was the error.

s=wb.Fullname ' <== Error here

Also, I may be opening .XLS or .XLSX workbooks. In either case, that is not my problem. The problem is with the wb object.

EDIT:
I double checked Excel 2010 help and the Application does support the ActiveWorkbook property, but when I run the macro I get an error "438: object doesn't support the property or method."

Do I have to check my VBA references? If so, which reference to I want to enable/check?

Under Code Window, Tools, References, these references are checked:

  1. Visual Basic for Applications
  2. Microsoft Excel 14.0 Object library (this is for 2010)
  3. OLE Automation
  4. Microsoft Office 14.0 Object Library (this is for 2010)
 
Last edited:
Well, here's my workaround. Apparently E2010 doesn't like the wb variable defined as a Workbook.
Code:
'Dim wb As Workbook
Dim s As String

'Set wb = Application.ActiveWorkbook ' No longer valid in E2010.

s = Application.ActiveWorkbook.FullName
s = Replace(s, ".xlsx", ".dat")
s = Replace(s, ".xls", ".dat")
ActiveWorkbook.SaveAs Filename:=s, FileFormat:=xlText, CreateBackup:=False
MsgBox "Saved tab-delim file as " & s
 

Users who are viewing this thread

Back
Top Bottom