asked on

Saving Text as XLS

I'm trying to import a text file (Working Well) but when I go to save it I get "Object variable or With block variable not set"

Private Function XLSConvertTEXT(strSource As String, strTarget As String)

'Create a new instance of Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim saveFile As Variant

Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.ActiveWorkbook
'Open the text file
'Set oBook = oExcel.workbooks.Open(strSource)
'Batch Method
'The Array(X,Y) in field info represents the column and data type
'Where X represents the column, Y represents the data type
'1 = General; 2 = Text; 3 = Date (MMDDYY)
oExcel.Visible = True
oExcel.Workbooks.OpenText strSource, 437, 1, 1, 1, False, True, False, False, False, True, ","
'FieldInfo:=Array(Array(1, 1), Array(2, 1), _
'Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
'Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _
'16, 1)), TrailingMinusNumbers:=True
oExcel.Columns.AutoFit
'I tried both with the Excel object and with the Book object
oBook.SaveAs strTarget, 1
oExcel.SaveAs strTarget, 1
'oBook.SaveAs fileName:=Left(strTarget, Len(strTarget) - 3) & "xls", FileFormat:=43, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
oExcel.ActiveWorkbook.Close
oExcel.Application.SaveAs fileName:=strTarget, FileFormat:=43
oBook = Nothing
oExcel.Quit
oExcel = Nothing

End Function

Private Function XLSConvertTEXT(strSource As String, strTarget As String)
 
    'Create a new instance of Excel
    Dim oExcel As Object
    Dim oBook As Object
    Dim oSheet As Object
    Dim saveFile As Variant
    
    Set oExcel = CreateObject("Excel.Application")
    Set oBook = oExcel.ActiveWorkbook
    'Open the text file
    'Set oBook = oExcel.workbooks.Open(strSource)
    'Batch Method
    'The Array(X,Y) in field info represents the column and data type
    'Where X represents the column, Y represents the data type
    '1 = General; 2 = Text; 3 = Date (MMDDYY)
    oExcel.Visible = True
    oExcel.Workbooks.OpenText strSource, 437, 1, 1, 1, False, True, False, False, False, True, ","
    'FieldInfo:=Array(Array(1, 1), Array(2, 1), _
    'Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
    'Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _
    '16, 1)), TrailingMinusNumbers:=True
    oExcel.Columns.AutoFit
    oBook.SaveAs strTarget, 1
    'oBook.SaveAs fileName:=Left(strTarget, Len(strTarget) - 3) & "xls", FileFormat:=43, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
    oExcel.ActiveWorkbook.Close
    oExcel.Application.SaveAs fileName:=strTarget, FileFormat:=43
    oBook = Nothing
    oExcel.Quit
    oExcel = Nothing
 
End Function

Open in new window

ASKER CERTIFIED SOLUTION

GrahamSkan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RalphOrtiz

ASKER

Now Im getting "Compile error: User-defind type not defined". Do I need to active a reference or componet?
it's stopping on the following:
Dim oExcel As Excel.Application 'Object
Dim oBook As Excel.Workbook 'Object
Dim oSheet As Excel.Worksheets 'Object

SOLUTION

GrahamSkan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RalphOrtiz

ASKER

GrahamSkan:

Thanks for pointing out the order in which the objects are initiated. The only change a had to move back to was as follows:
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

This eliminated the error I encountered with Compile error: User-defind type not defined.

Again Thank You
.

RalphOrtiz

ASKER

I want to personally thank you for your help I able to continue my project without much delay. I must say this is a great resource keep the great work!

Ralph

GrahamSkan

Yes. You switched back to late binding instead of setting the reference.

I find that difficult to develop in because you don't get any access to the application's (Excel's) Help or intellisense and you can't browse the library with the F2 key.