CODEWITHSUNDEEP
excelvba
vlad_milovanovic
vlad_milovanovic

Reputation: 31

How to write text to a cell after checking if file exists in a folder?

I am checking if a file exists in a folder and if yes, instead of a message box, I would like to write text (e.g. "Extracted") in a cell. The code works fine the message box, but once I try to replace those with actual cells, nothing gets written. Any idea what is happening?

Dim FileName As String

FileName = VBA.FileSystem.Dir(ExtractFolderPath & ExtractFileName)

If FileName = VBA.Constants.vbNullString Then
    ThisWorkbook.Worksheets(1).Cells(ThisWorkbook.Worksheets(1).Cells(1, 12), 4).Value = ""
Else
    ThisWorkbook.Worksheets(1).Cells(ThisWorkbook.Worksheets(1).Cells(1, 12), 4).Value = "Extracted"
End If

Upvotes: 0

Views: 298

Answers (2)

iDevlop
iDevlop

Reputation: 25262

Try decomposing the problem: 

Dim FileName As String, rng as range, sh as worksheet

FileName = Dir(ExtractFolderPath & ExtractFileName)
set sh = Thisworkbook.WorkSheets(1)
set rng =  sh.Cells(.Cells(1, 12), 4)
debug.print rng.address, rng.value2
with rng
    If FileName = vbNullString Then
        .Value = ""
    Else
        .Value = "Extracted"
    End If
end with

Upvotes: 0

TourEiffel
TourEiffel

Reputation: 4424

As you said that your if statment is working fine with MsgBox Maybe the error is here.

Give this code a try :

Dim FileName As String

FileName = VBA.FileSystem.Dir(ExtractFolderPath & ExtractFileName)

If FileName = VBA.Constants.vbNullString Then
    ThisWorkbook.Worksheets(1).Cells(ThisWorkbook.Worksheets(1).Cells(1, 12).Value, 4).Value = ""
Else
    ThisWorkbook.Worksheets(1).Cells(ThisWorkbook.Worksheets(1).Cells(1, 12).Value, 4).Value = "Extracted"
End If

Else if this doesn't work you need to be sure that :

ThisWorkbook.Worksheets(1).Cells(1, 12).Value

Is a number so you can I think use this : 

debug.print IsNumeric(ThisWorkbook.Worksheets(1).Cells(1, 12).Value)

It should returns you True

Upvotes: 1

Related Questions