0

I have a vba code in access that gets data from an excel page and transfers it in excel through variables using a button in an access form. If some data requested aren't in the excel file, I want it to show a msgbox showing me which data weren't transfered. To do that I'm using boolean variables and with "IF" I check if the variable with the stored data from excel is empty.

Var1IF = True
Var1 = Sheet1.Cells(95, 80).Value
If Var1 = "" Then
    Var1IF = False
End If

No matter what I do Var1IF always returns as False. Even if Var1 has data in it or not (Since the cells from excel might have both numbers and letters, Var1 is a String).

I even did the opposite and it returned False again:

Var1IF = True
    Var1 = Sheet1.Cells(95, 80).Value
    If Var1 <> "" Then
        Var1IF = False
    End If

Edit: Declarations:

Dim oXLApp As Object, wb As Object
Dim Sheet1 As Object
Dim Var1 as String
Dim Var1IF as Boolean

Set oXLApp = GetObject(, "Excel.Application")
Set Sheet1 = oXLApp.Sheets("Sheet1")
Community
  • 1
  • 1
Panagiotis Gianniotis
  • 17
  • 1
  • 10
  • `var1` is simply not an empty string; what does `debug.print Sheet1.Cells(95, 80).Value, len(Sheet1.Cells(95, 80).Value)` show? (ctrl+g to see output) – Alex K. Feb 23 '16 at 11:24
  • It prints the value of that cell. I know var1 is simply not an empty string. Then why does Var1IF return False since the IF condition doesn't pass ? For some reason in my code Var1="" and Var1<>"" is always correct. – Panagiotis Gianniotis Feb 23 '16 at 11:28
  • I have the feeling we are not seeing the full picture. Please add your variable declarations to the code (what datatypes are Var1, Sheet1 ?) – Andre Feb 23 '16 at 12:14
  • sheet1 is different than thisworkbook.sheets(1). The latter is proper referencing. Know also that sheet1 has to do with vbe explorer, and not with worksheet from worbook. It just happens to be the same in "simple" worbooks. – Patrick Lepelletier Jun 20 '17 at 00:03

2 Answers2

0
Set Sheet1 = oXLApp.Sheets("Sheet1")

Shouldn't you open a workbook first? Are you sure Sheet1 actually refers to something?

Anyway, please try this code:

Var1 = Sheet1.Cells(95, 80).Value
Debug.Print "Value: <" & Var1 & ">"
If Var1 = "" Then
    Debug.Print "Var1 is empty string"
End If
If Var1 <> "" Then
    Debug.Print "Var1 is NOT empty string"
End If

and check the Immediate window. I would be rather surprised if you get both "Var1" debug lines.

Andre
  • 26,751
  • 7
  • 36
  • 80
  • As for your first question, in order for the button to work you have to have the excel file opened by urself (since it's not one specific excel file, the user decides which one). Secondly, as I've answered my own question, I fixed the problem by saving the data from Var1 to another variable and for some ridiculous reason it worked. – Panagiotis Gianniotis Feb 23 '16 at 12:48
  • @PanagiotisGianniotis: Hmm, ok. I guess this question should be closed as not reproducible. – Andre Feb 23 '16 at 13:13
0

This is just ridiculous. I saved the data from Var1 to another variable after getting it from excel and the problem solved itself. I checked it about 10 times and it just did work while the previous way didn't.

Panagiotis Gianniotis
  • 17
  • 1
  • 10