Solved

Excel save as file format issue

Posted on 2007-12-06
7
4,900 Views
Last Modified: 2008-02-01
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
Comment
Question by:gabulish
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
7 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20423390
just use -4143

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

Author Comment

by:gabulish
ID: 20423939
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
 
LVL 25

Expert Comment

by:imitchie
ID: 20423973
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:gabulish
ID: 20428348
What does the -4143 stand for?
0
 
LVL 25

Accepted Solution

by:
imitchie earned 350 total points
ID: 20431151
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
 
LVL 1

Expert Comment

by:Computer101
ID: 20632931
Forced accept.

Computer101
EE Admin
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question