[RESOLVED] Help in writing data to an open workbook in specific columns

In my code below, I am searching the active sheet with the data in an array. When I find the data in the active sheet, I want to paste its address in column A, and the value in column B on my "validation.xls" file, on sheet1. I feel the best place to do this is in my Do loop but I am unsure on how to write to my open validation.xls file, how to write the address in column a, the value in column B, and on the next Do iteration, do the same on the next available row on my validation.xls - sheet1. Could anyone help me with that? Thanks!

Code:

---

Public Sub ec_es_validation()
    Dim MyData As DataObject
    Dim myStr As String
    Dim myStrArray As Variant
    Dim i As Long, x As Long
    Dim wBook As Workbook
    Dim c As Range, Search_Range As Range
     
    fileExists = False
   
    Set MyData = New DataObject
    MyData.GetFromClipboard
    myStr = MyData.GetText(1)
   
    wbOpen = 1
    'Does the validation.xls exist?
    If Not Dir("C:\Temp\validation.xls", vbDirectory) = vbNullString Then 'yes file exists
        'checks to see if the document is open
        On Error Resume Next
        Set wBook = Workbooks("C:\Temp\validation.xls")
        'is file open?
        If wBook Is Nothing Then 'Not open
            Workbooks.Open "C:\Temp\validation.xls"
            Set wBook = Nothing
            On Error GoTo 0
        End If
    Else 'validation.xls does not exist
        'creates the validation.xls spreadsheet
        Set NewBook = Workbooks.Add
        With NewBook
            .Title = "validation"
            .SaveAs Filename:="C:\Temp\validation.xls"
        End With
    End If
 
    myStrArray = Split(myStr, vbNewLine)
   
    For i = LBound(myStrArray) To UBound(myStrArray)
        'myStrArray(i)
        With Search_Range
            Set c = Cells.Find(What:=myStrArray(i), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
            If Not c Is Nothing Then
                Set Find_Range = c
                    FirstAddress = c.Address
                    FirstColumn = c.Column
                Do
                    Set Find_Range = Union(Find_Range, c)
                    'write to validate.xls - sheet1 on the next unused cell in column a
                    Set c = Cells.FindNext(c)
                Loop While Not c Is Nothing And c.Address <> FirstAddress
            End If
        End With
    Next i
   
End Sub

---

Also as a side note, when i create my validation.xls file and try to open it manually i am getting a "your file is in a different format that what is specified by the extension. " What am I doing wrong? I have office 2k7 on my computer. Thanks!

depending if the workbook exists or is already open you end up with2 objects or no object change this portion then the workbook is always wbook

vb Code:

If Not Dir("C:\Temp\validation.xls", vbDirectory) = vbNullString Then 'yes file exists
        'checks to see if the document is open
        On Error Resume Next
        Set wBook = Workbooks("C:\Temp\validation.xls")
        'is file open?
        If wBook Is Nothing Then 'Not open
            Set wBook = Workbooks.Open "C:\Temp\validation.xls"
            On Error GoTo 0
        End If
    Else 'validation.xls does not exist
        'creates the validation.xls spreadsheet
        Set wBook = Workbooks.Add
        With wBook
            .Title = "validation"
            .SaveAs Filename:="C:\Temp\validation.xls"
        End With
    End If
    nextrow = wbook.sheets("sheet1").range("A65535").end(xlup).row + 1

you can now use the wbook object for all your results

vb Code:

wbook.sheets("sheet1").range("a" & nextrow) = c.address
wbook.sheets("sheet1").range("b" & nextrow) = c.value
nextrow = nextrow + 1

Quote:

---

Also as a side note, when i create my validation.xls file and try to open it manually i am getting a "your file is in a different format that what is specified by the extension. " What am I doing wrong? I have office 2k7 on my computer. Thanks!

---

i guess it is because you are using an xls file extension when you use saveAs, you need to either use 2007 extension or specify the correct file format for xls file

That did it! I have another question but I will post another topic. Thanks again!