asked on

Excel save as file format issue

After converting a .CSV to an Excel file the Excel file is saved as "Tab delimited". I need it saved as type "Microsoft Office Excel Workbook". I've tried the attached code but get the below error message.

{"Public member 'XlFileFormat' on type 'Application' not found."}

Reference::
http://msdn2.microsoft.com/en-us/library/microsoft.office.interop.excel.xlfileformat(VS.80).aspx

Thoughts?

Dim objWorkbook = objExcel.Workbooks.Open("C:\ExcelFiles\art.txt")
 
objExcel.ActiveWorkbook.SaveAs("C:\test.xls", objExcel.XlFileFormat.xlWorkbookNormal)

Open in new window

imitchie

just use -4143

objExcel.ActiveWorkbook.SaveAs("C:\test.xls", -4143)

gabulish

ASKER

That works but it causes my code to return an error further down in the function:

 '*****Create Application Spreadsheets
        Dim objExcel = CreateObject("Excel.Application")
        Dim objExcel2 = CreateObject("Excel.Application")
 
        '*****Do not prompt to Save Clipboard Information
        objExcel.DisplayAlerts = False
        objExcel2.DisplayAlerts = False
 
        '*****Added to save changes without being prompted by Excel
 
        Const xlSaveChanges = 1
        Const ForReading = 1
        Const ForWriting = 2
 
        '*****Create Variables for Date/Time Constants
 
        Dim strXlDumpFile = "C:\ExcelF\Dump_" & strMonth & strDay & strYear & strHour & strMin & ".xls"
        Dim strXlHeaderFile = "C:\ExcelF\header.xls"
 
        '****************Execute******************************
        '*****Open .TXT File and Delimit
 
        Dim objFSO = CreateObject("Scripting.FileSystemObject")
        Dim objFile = objFSO.OpenTextFile("C:\ExcelF\test.txt", ForReading)
        Dim strContents = objFile.ReadAll
        objFile.Close()
 
        strContents = Replace(strContents, "|", vbTab)
 
        objFile = objFSO.OpenTextFile("C:\ExcelF\test.txt", ForWriting)
        objFile.Write(strContents)
        objFile.Close()
 
        '*****Convert .TXT File to .XLS File
 
        Dim objWorkbook = objExcel.Workbooks.Open("C:\ExcelF\test.txt")
 
        objExcel.ActiveWorkbook.SaveAs(strXlDumpFile, -4143)
        objWorkbook.Close(xlSaveChanges)
        objExcel.Quit()
 
        '*****Open Headerfile and Copy Row
        Dim objWorkbook2 = objExcel2.Workbooks.Open(strXlHeaderFile)
 
        Dim wsA = objWorkbook2.WorkSheets.item("header")
 
        wsA.Activate()
        wsA.Rows.item(1).Select()
        objExcel2.Selection.Copy()
 
        '*****Paste Headerfile into DumpFile
        Dim objExcelX = CreateObject("Excel.Application")
        Dim objWorkbookX = objExcelX.Workbooks.Open("C:\ExcelF\Dump_" & strMonth & strDay & strYear & strHour & strMin & ".xls")
 
****I get this error on the next line
****Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))
****
        Dim wsB = objWorkbookX.WorkSheets.item("Dump_" & strMonth & strDay & strYear & strHour & strMin)
 
 
 
        wsB.Rows(1).Insert()
        wsB.Activate()
        wsB.Rows.item(1).Select()
        wsB.paste()
 
        '*****Close Excel File without prompt to Save
 
        objWorkbookX.Close(xlSaveChanges)
        objWorkbook2.Close(xlSaveChanges)
        objExcel.Quit()
 
        Return MsgBox(".CSV to .XLS conversion complete")
 
    End Function

Open in new window

imitchie

I'm sorry - it is not the "cause"!
Verify that the workbook does exist and is saved correctly. Open it up, and check that there is a worksheet named
"Dump_" & strMonth & strDay & strYear & strHour & strMin

(there probably isn't)
Probably best to just to item(1) // first sheet

gabulish

ASKER

What does the -4143 stand for?

ASKER CERTIFIED SOLUTION

imitchie

membership

This solution is only available to members.

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

Start Free Trial

Computer101

Forced accept.

Computer101
EE Admin