Avatar of RalphOrtiz
RalphOrtizFlag for United States of America 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

Visual Basic ClassicVB Script

Avatar of undefined
Last Comment
GrahamSkan

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
GrahamSkan

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
RalphOrtiz

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
RalphOrtiz

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
.
ASKER
RalphOrtiz

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
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.