CODEWITHSUNDEEP
excelvba
Lalaland
Lalaland

Reputation: 308

Kill Opened CSV File

So i have code to copy data from one csv to another open Excel xlsx file but what i am trying to do is to kill the csv file once process is done. the code works but the kill part is not working, what am i doing wrong here?

Sub MasterData()
Dim getfilestg As String

    Application.Calculation = xlManual
    Application.ScreenUpdating = False
    ActiveSheet.UsedRange.ClearContents
    Application.ScreenUpdating = True
    Range("A1").Select

Application.ScreenUpdating = False


Set reporting = ActiveWorkbook

Set rep = Sheets("Master Data")


 For Each reportSF In Application.Workbooks
        reportSF.Activate

        If Range("A1").Value Like "GoldTier ID" And Range("AT1").Value Like "Owner's TL" Then reportSF.Activate: Exit For
        Next reportSF
If Not reportSF Is Nothing Then

reporting.Activate
rep.Activate
endLine = Range("A1").Offset(Rows.Count - 1, 0).End(xlUp).Row

 reportSF.Activate
endLine = Range("A1").Offset(Rows.Count - 1, 0).End(xlUp).Row
reportSF.Sheets(1).Range("A1:AT" & endLine).Copy
reporting.Activate
rep.Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False

End If

If reportSF.ActiveCell.Value = "Account" Then
getfilestg = Application.GetOpenFilename("Excel Files (*.csv), *.csv")

Kill (getfilestg)
End If

Range("A1").Select


End Sub

Upvotes: 0

Views: 406

Answers (1)

Ricards Porins
Ricards Porins

Reputation: 384

replace the following code:

If reportSF.ActiveCell.Value = "Account" Then
getfilestg = Application.GetOpenFilename("Excel Files (*.csv), *.csv")

Kill (getfilestg)
End If

with:

If reportSF.Sheet(1).Range("A1").Value = "Account" Then '<-- change range here
getfilestg = reportSF.FullName
reportSF.Close
Kill (getfilestg)
End If

This will close and delete reportSF

Upvotes: 1

Related Questions