Click here to Skip to main content
15,443,778 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Updated 17-Sep-21 2:25am
v2

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.
 
Share this answer
 
Comments
Devendra Sarang 17-Sep-21 7:58am    
Dim xlapp As Objectxlapp = CreateObject("Excel.Application")Dim xlWorkBook As Excel.WorkbookDim xlWorkSheet As Excel.WorksheetDim 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 IfIf String.IsNullOrEmpty(xlWorkBook.Path) Then	xlWorkBook.SaveAs(FilePath, Excel.XlFileFormat.xlWorkbookNormal)End IfIf xlWorkBook.Sheets.Count() < 1 Then	xlWorkSheet = CType(xlWorkBook.Worksheets.Add(), Excel.Worksheet)	xlWorkSheet.Name = "Log Sheet"Else	xlWorkSheet = xlWorkBook.Worksheets(1)End IfDim 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 IfNext  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                NextxlWorkBook.Save()xlApp.DisplayAlerts = FalsexlWorkBook.Close()System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)xlWorkSheet = NothingSystem.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook)xlWorkBook = NothingSystem.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)xlApp = Nothing
Devendra Sarang 17-Sep-21 7:59am     CRLF
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
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.
 
Share this answer
 
Comments
Devendra Sarang 17-Sep-21 7:42am     CRLF
yes. our main requirement is to share file on shared drive so will change the file path.
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
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900