The file format and the extension of .xls dont match

Question

0.00/5 (No votes)

See more:

Excel-2016

I am trying to maintain simple log file on button click on form.

I am able to to do perfectly but there is one error when opening log excel file.

Its is saying that "The file format and the extension of .xls don't match."

My code is something like that.

On button click, it will check whether there is log file on "C:\" exists or not

If it is exists then it will edit new log data

if it is not exists then it will create new excel file, add required column and add log information.

My issue is only the error message which i am getting when try to open that log excel file

I am opening it in office 2016

Error Message[^]

What I have tried:

VB

Dim xlapp As Object
xlapp = CreateObject("Excel.Application")

Dim xlWorkBook As Excel.Workbook

Dim xlWorkSheet As Excel.Worksheet

Dim FilePath As String = "C:\1.xls"
           

If System.IO.File.Exists(FilePath) Then

Else
	System.IO.File.Create(FilePath).Close()
End If

 xlWorkBook = xlapp.Workbooks.Open(FilePath, False)


                If xlWorkBook.Sheets.Count() < 1 Then
                    xlWorkSheet = CType(xlWorkBook.Worksheets.Add(), Excel.Worksheet)
                    xlWorkSheet.Name = "Log Sheet"

                Else
                    xlWorkSheet = xlWorkBook.Worksheets(1)
                End If

 Dim SheetRow As Integer = 0

                For SheetRow = 1 To xlWorkSheet.Rows.Count
                    If Trim(xlWorkSheet.Cells(SheetRow, 1).value) = "" Then
                        xlWorkSheet.Name = "Log Sheet"

					    xlApp.Cells(1, 1) = "File access path "
                        xlApp.Cells(1, 2) = "File Name"
                        xlApp.Cells(1, 3) = "File Access By User"
                        xlApp.Cells(1, 4) = "Date"
                        xlApp.Cells(1, 5) = "Time"

                        xlWorkSheet.Columns.Range("A1:B1").Font.Bold = True
                        xlWorkSheet.Columns.Range("A1:B1").Font.Color = Color.Blue
                        xlWorkSheet.Columns.Range("A1:B1").Font.Size = 45

                        Exit For
                    End If
                Next		


                Dim iRow As Integer = 0
                Dim iCol As Integer = 0

                For iRow = 3 To xlWorkSheet.Rows.Count
                    If Trim(xlWorkSheet.Cells(iRow, 1).value) = "" Then

                        xlWorkSheet.Cells(iRow, 1).Value = sDirectory
                        xlWorkSheet.Cells(iRow, 2).Value = sFileName

                        Dim AccessDate As String = String.Empty
                        Dim AccessTime As String = String.Empty

                        AccessDate = AccessDate.Trim & DateTime.Now.Day
                        AccessDate = AccessDate.Trim & "-" & DateTime.Now.ToString("MMMM")
                        AccessDate = AccessDate.Trim & "-" & DateTime.Now.Year & " "

                        AccessTime = AccessTime.Trim & DateTime.Now.Hour
                        AccessTime = AccessTime.Trim & ":" & DateTime.Now.Minute & " "
                        AccessTime = AccessTime.Trim & " " & DateTime.Now.ToString("tt")


                        xlWorkSheet.Cells(iRow, 3).Value = My.Settings.timeSheetLoginUserType
                        xlWorkSheet.Cells(iRow, 4).Value = AccessDate
                        xlWorkSheet.Cells(iRow, 5).Value = AccessTime

                        Exit For        ' BAIL OUT IF REACHED THE LAST ROW.
                    Else
                        
                    End If

                Next				

xlWorkBook.Save()
xlApp.DisplayAlerts = False


xlWorkBook.Close()

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
xlWorkSheet = Nothing

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook)
xlWorkBook = Nothing

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
xlApp = Nothing

Posted 16-Sep-21 23:06pm

Devendra Sarang

Updated 17-Sep-21 2:25am

v2

Add a Solution

3 solutions

Solution 3

In response to your comment

Quote:
Still i want to ask. Is there is some logical or other issue in code.

You are using Excel/Office 2016 so you should be using .xlsx files and not the old style .xls. Excel will still open the files, but in Compatibility Mode.

The code

VB

If xlWorkBook.Sheets.Count() < 1 Then

is not relevant - either the log file already exists - in which case it already has a Worksheet "Log Sheet" or you created a new Workbook with

VB

xlWorkBook = xlapp.workbooks.add()

In the latter case, Excel will have automatically created a worksheet called "Sheet1" - which you will want to rename to "Log Sheet". Every workbook has to have at least one visible worksheet so your If statement will never be true

Posted 17-Sep-21 2:25am

CHill60

Solution 2

In addition to what Richard has said, I'll add this:

Quote:
On button click, it will check whether there is log file on "C:\" exists or not

If it is exists then it will edit new log data
...
Dim FilePath As String = "C:\1.xls"

Never store information in the root of a drive, and particularly not in the root of the boot drive: they are protected to reduce or prevent virus activity and it can giove you some real problems with more recent OSes.

See here: See here: Where should I store my data?[^] for some better ideas.

Posted 17-Sep-21 1:22am

OriginalGriff

Comments

Devendra Sarang 17-Sep-21 7:42am

yes. our main requirement is to share file on shared drive
so will change the file path.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2021-09-17T00:45:00

Solution 1

Quote:

VB.NET

If System.IO.File.Exists(FilePath) Then

Else
    System.IO.File.Create(FilePath).Close()
End If

You have created an empty file, which is not a valid Excel .xls file. When you try to open that file, you will get a warning from Excel.

Change your code so that you create a new workbook when the file doesn't exist, rather than trying to open an invalid file:

VB.NET

If System.IO.File.Exists(FilePath) Then
    xlWorkBook = xlapp.Workbooks.Open(FilePath, False)
Else
    xlWorkBook = xlapp.Workbooks.Add()
End If

...

If String.IsNullOrEmpty(xlWorkBook.Path) Then
    xlWorkBook.SaveAs(FilePath, XlFileFormat.xlExcel8)
Else
    xlWorkBook.Save()
End If

NB: You need to specify the file format when you first save the file, since you're not using the default format.

Posted 17-Sep-21 0:45am

Richard Deeming

Comments

Devendra Sarang 17-Sep-21 7:58am

Dim xlapp As Object
xlapp = CreateObject("Excel.Application")

Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet

Dim FilePath As String = "C:\1.xls"


If System.IO.File.Exists(FilePath) Then
	xlWorkBook = xlapp.workbooks.open(FilePath, False)
Else
	xlWorkBook = xlapp.workbooks.add()
End If


If String.IsNullOrEmpty(xlWorkBook.Path) Then
	xlWorkBook.SaveAs(FilePath, Excel.XlFileFormat.xlWorkbookNormal)
End If

If xlWorkBook.Sheets.Count() < 1 Then
	xlWorkSheet = CType(xlWorkBook.Worksheets.Add(), Excel.Worksheet)
	xlWorkSheet.Name = "Log Sheet"
Else
	xlWorkSheet = xlWorkBook.Worksheets(1)
End If

Dim SheetRow As Integer = 0

	For SheetRow = 1 To xlWorkSheet.Rows.Count
		If Trim(xlWorkSheet.Cells(SheetRow, 1).value) = "" Then
					xlWorkSheet.Name = "Log Sheet"

                        xlApp.Cells(1, 1) = "File access path "
                        xlApp.Cells(1, 2) = "File Name"
                        xlApp.Cells(1, 3) = "File Access By User"
                        xlApp.Cells(1, 4) = "Date"
                        xlApp.Cells(1, 5) = "Time"

                        xlWorkSheet.Columns.Range("A1:E1").Font.Bold = True
                        xlWorkSheet.Columns.Range("A1:E1").Font.Color = Color.Blue
                        xlWorkSheet.Columns.Range("A1:E1").Font.Size = 15

                        Exit For
                    End If
Next

  Dim iRow As Integer = 0
                Dim iCol As Integer = 0

                For iRow = 3 To xlWorkSheet.Rows.Count
                    If Trim(xlWorkSheet.Cells(iRow, 1).value) = "" Then

                        xlWorkSheet.Cells(iRow, 1).Value = sDirectory
                        xlWorkSheet.Cells(iRow, 2).Value = sFileName

                        Dim AccessDate As String = String.Empty
                        Dim AccessTime As String = String.Empty

                        AccessDate = AccessDate.Trim & DateTime.Now.Day
                        AccessDate = AccessDate.Trim & "-" & DateTime.Now.ToString("MMMM")
                        AccessDate = AccessDate.Trim & "-" & DateTime.Now.Year & " "

                        AccessTime = AccessTime.Trim & DateTime.Now.Hour
                        AccessTime = AccessTime.Trim & ":" & DateTime.Now.Minute & " "
                        AccessTime = AccessTime.Trim & " " & DateTime.Now.ToString("tt")


                        xlWorkSheet.Cells(iRow, 3).Value = My.Settings.timeSheetLoginUserType
                        xlWorkSheet.Cells(iRow, 4).Value = AccessDate
                        xlWorkSheet.Cells(iRow, 5).Value = AccessTime



                        Exit For        ' BAIL OUT IF REACHED THE LAST ROW.
                    Else
                        
                    End If

                Next

xlWorkBook.Save()
xlApp.DisplayAlerts = False

xlWorkBook.Close()

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
xlWorkSheet = Nothing

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook)
xlWorkBook = Nothing

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
xlApp = Nothing

Devendra Sarang 17-Sep-21 7:59am

this is final code after changes as per your suggestion.

It is working perfectly and that "The file format and the extension" message issue was also solved.

Still i want to ask. Is there is some logical or other issue in code.

Right now it is working perfectly.

CHill60 17-Sep-21 8:25am

I have added as solution in response to this comment