troubleshooting Question

Saving Text as XLS

Avatar of RalphOrtiz
RalphOrtizFlag for United States of America asked on
Visual Basic ClassicVB Script
6 Comments1 Solution807 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros