CODEWITHSUNDEEP
excelvba
Damu Kesavath
Damu Kesavath

Reputation: 21

How to read a cell value from an excel file

I have a folder that contains one excel workbook each for each employee (only one worksheet in the workbook). I wanted open each of the employee workbook and read the cell in $A$1, and write to another worksheet. Ultimately, I will have a worksheet which will contain all $A$1 cells each of the workbook. I wrote the following code, but the line I indicated is not working for some reason. Can you suggest how to do this? Thanks

Private Sub CommandButton1_Click()
Const FOLDER As String = "c:\Junk\Employee Files\"

On Error GoTo ErrorHandler

Dim i As Integer
i = 0

Dim fileName As String

fileName = Dir(FOLDER, vbDirectory)
   Do While Len(fileName) > 0

    If Right$(fileName, 4) = "xlsx" Then
        i = i + 1

        Dim currentWkbk As Excel.Workbook
         Set currentWkbk = Excel.Workbooks.Open(FOLDER & fileName)
         Cells(i, 1) = "Employee " & 1
        'The line above works perfectly
         Cells(i, 2) = currentWkbk.Range("A1").Value   
        'The line doesn't work.  above works perfectly
    End If
    fileName = Dir
  Loop

ProgramExit:
   Exit Sub
ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit

End Sub

Upvotes: 2

Views: 7687

Answers (3)

Ajeet Verma
Ajeet Verma

Reputation: 1123

I have come to a new challenge in which we received 100 of excel files ,in the file at the cell A3:A3 there is requester of file name is there . So I have to load the data only for a specific requester ,and the data start in the file from Cell A35.

enter image description here

I have now created a for each loop in that loop, I'm saving the filename and sheet name into the variable and using that variable at the time of reading data from variable. Now I have created another variable and in expression I have use the syntax:"SELECT "+ "["+@[User::SheetName]+"A3:A3]"+".F1" +" AS COL1 FROM "+ "["+@[User::SheetName]+"A3:A3]"

enter image description here

Now SET the ResultSet=Single Row and assign the result set to P_id which is another variable. Now in precedence constraint we equate that variable ,if value is matching the it will pass the flow else we can move or delete that file based on the condition:

enter image description here

Upvotes: 0

Jook
Jook

Reputation: 4682

Brad does have a point, but I think your error is more likely due to the lack of a specified worksheet - try to use this:

Cells(i, 2) = currentWkbk.worksheets(1).Range("A1").Value

instead of this

Cells(i, 2) = currentWkbk.Range("A1").Value

However, you should think about using Brads suggestion too ;)

Some might suggest to use a dynamic solution for the worksheet selection. I did not use one, because you explicitly said, there will be only one worksheet. But regardless, you can of course make it dynamic or use the actual name of the sheet instead of 1 - whatever suits your needs.

Upvotes: 2

Brad
Brad

Reputation: 12255

You need to fully qualify your range references. Since you are referencing two workbooks you can't use terms like cells(i,2) because that defaults to referencing the active workbook which is usually not what you want and in this case in particular when you open your new file with Set currentWkbk = Excel.Workbooks.Open(FOLDER & fileName) it became the active workbook.

Try instead something like this at the beginning of your routine

Dim destWorksheet as Worksheet
Set destWorksheet = Thisworkbook.Sheets("Name of sheet")

Then use it like

If Right$(fileName, 4) = "xlsx" Then
    i = i + 1

    Dim currentWkbk As Excel.Workbook
     Set currentWkbk = Excel.Workbooks.Open(FOLDER & fileName)
     destWorksheet.Cells(i, 1) = "Employee " & 1
    'The line above works perfectly
     destWorksheet.Cells(i, 2) = currentWkbk.Range("A1").Value   
    'The line doesn't work.  above works perfectly
End If

Upvotes: 0

Related Questions