0

I have a Visual Basic application where I open an Excel spreadsheet, process the data, and then exit the application. Since this application will be started with a scheduled task, and I don't want to process the spreadsheet if a new one has not been created, I want to change the file name from filename.xls to filename.xxx after processing. Then, if filename.xls is not present the next time the application runs, no processing will occur. This is the code I am executing:

Dim useXlsFile As String = "N"
Dim xlsFileName As String = "C:\VPA_FTP\ValuePoint\incoming\VW_OUT_PST_ACCURECORD_CREDS.xls"

Dim currdate As String = Now.Year
If Now.Month < 10 Then
    currdate = currdate & "0" & Now.Month
Else
    currdate = currdate & Now.Month
End If
If Now.Day < 10 Then
    currdate = currdate & "0" & Now.Day
Else
    currdate = currdate & Now.Day
End If
Dim renameXlsFileName = "VW_OUT_PST_ACCURECORD_CREDS" & currdate & ".xxx"
If My.Computer.FileSystem.FileExists(xlsFileName) Then
    useXlsFile = "Y"
Else
    MsgBox("File not found.")
End If

If useXlsFile = "Y" Then
    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    xlApp = New Excel.Application
    xlWorkBook = xlApp.Workbooks.Open(xlsFileName)
    xlWorkSheet = xlWorkBook.ActiveSheet

    Dim range As Excel.Range
    range = xlWorkSheet.UsedRange
    numberOfDataRecords = range.Rows.Count
    numberOfColumns = range.Columns.Count

    xlApp.DisplayAlerts = False
    xlWorkBook.Saved = True
    xlWorkBook.Close(True)
    xlApp.Quit()

    Try
        My.Computer.FileSystem.RenameFile(xlsFileName, renameXlsFileName)
    Catch ex As Exception
        ReturnValue = ex.Message
        Dim ReturnValuex As Exception
        ReturnValuex = ex.InnerException
    End Try
End If

Application.Exit()

When I execute the code, the Catch is executed when trying to rename the file. I get this error message

The process cannot access the file because it is being used by another process.

I was under the impression that the xl.App.Quit() would close the file but I guess not.

How can I close the file so I can rename it?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jonathan Small
  • 1,027
  • 3
  • 18
  • 40
  • 1
    https://stackoverflow.com/q/46303946/11683? – GSerg Sep 07 '19 at 15:30
  • Renaming a file that is in use is not normally a problem since it only changes the directory entry and not the file data. Anti-malware can act up. But it is pretty likely that Excel takes extra counter-measures, it worries about multiple users trying to edit the same file and generating a good notification when that happens. Based on the filename, renaming it would ruin that. So nothing much else you can do but ensuring that Excel terminates when you ask it to, [look here](https://stackoverflow.com/a/25135685/17034). – Hans Passant Sep 07 '19 at 15:57
  • Please refer to [The proper way to dispose Excel com object using VB.NET?](https://stackoverflow.com/a/38111107/1115360). Having done that, there will no longer be a lock on the file to prevent you from renaming it. – Andrew Morton Sep 07 '19 at 18:51
  • please try to use 'saveas' instead, or yo can try to add xlapp=nothing, xlWorkBook = nothing and WorkSheet=nothinf after xlapp.quit before renaming the file. To use save as: xlWorkBook.SaveAs (renameXlsFileName) – user11982798 Sep 08 '19 at 00:05
  • Please turn on Option Strict. This is a 2 part process. First for the current project - In Solution Explorer double click My Project. Choose Compile on the left. In the Option Strict drop-down select ON. Second for future projects - Go to the Tools Menu -> Options -> Projects and Solutions -> VB Defaults. In the Option Strict drop-down select ON. This will save you from bugs at runtime. I didn't have to look too far in your code to know it isn't on. `Dim currdate As String = Now.Year` will not compile with Option Strict. – Mary Sep 08 '19 at 01:08
  • Replace the code from `Dim currdate As String = Now.Year` to `currdate = currdate & Now.Day End If` with `Dim DateString = Now.ToString("yyyyMMdd")` – Mary Sep 08 '19 at 01:18

0 Answers0