Solved

Excel save as file format issue

Posted on 2007-12-06
7
4,886 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
  • 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
combo box display member not working 11 21
.Net Web Site Password specs 2 21
Get String split 5 33
Events in static methods 3 31
This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now