• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4911
  • Last Modified:

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

0
gabulish
Asked:
gabulish
  • 3
  • 2
1 Solution
 
imitchieCommented:
just use -4143

objExcel.ActiveWorkbook.SaveAs("C:\test.xls", -4143)
0
 
gabulishAuthor Commented:
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

0
 
imitchieCommented:
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gabulishAuthor Commented:
What does the -4143 stand for?
0
 
imitchieCommented:
It stands for XlFileFormat.xlWorkbookNormal. but within excel, it knows that "XlFileFormat.xlWorkbookNormal" = -4143
Outside of excel, "XlFileFormat.xlWorkbookNormal" may not mean anything to other programs. So, just use the internal number
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now