Solved

Open Excel File, read worksheets, select last number in column

Posted on 2014-12-20
10
124 Views
Last Modified: 2014-12-31
Hello Experts,
I need help to write the macro. I've started with a very basic script, but need help to fill in the details. Let me know if this is too much, I can break the steps into several requests. (sample files attached)

Details:
From the Accounts file, I need to open DataFile1.xlsx and select each worksheet, read the final number in column Y, then copy that corresponding number back to the Accounts file. If the worksheet name in the DataFile1 is not listed in Accounts, then a row needs to be inserted in Accounts with that worksheet name.

Outline:


Sub OpenFile()
  'from the highlighted column, select the pathname and filename in rows 7 & 8, open the file
  Workbooks.Open Filename:=Pathname&Filename
 'read the worksheet names,
 'If not found in Accounts, then insert row() with worksheet name. EndIf
 'Find last number in column Y, copy to Accounts of corresponding row
End Sub

Thanks,

Ron
Accounts.xlsx
DataFile1.xlsx
0
Comment
Question by:bikeski
  • 6
  • 4
10 Comments
 
LVL 35

Expert Comment

by:Kimputer
ID: 40511205
This code should have almost everything you specified (can't figure out what you mean with 'If not found in Accounts, then insert row() with worksheet name. EndIf' yet.
For now my code searches, if found write it down. If not found, then add to a string at the last row (so in Accounts, cell A12 should say something like "list of sheet names not found" or something):

Sub test()

Application.ScreenUpdating = False
Dim cw, nw As Workbook
Dim cs, ns As Worksheet

Set cw = ActiveWorkbook
Set cs = cw.Sheets(1)

lastrow = cs.Cells(cs.Rows.Count, "A").End(xlUp).Row 'important for sheet names check
lastcol = cs.Cells(7, cs.Columns.Count).End(xlToLeft).Column 'to check how many files to open and read

For cols = 2 To lastcol Step 1
    'open file
    stringcounter = 0
    Set nw = Workbooks.Add(cs.Cells(7, cols).Value & "\" & cs.Cells(8, cols).Value)
    For Each foundsheet In nw.Sheets
        foundsheet_bool = False
        For foundrows = 10 To lastrow Step 1
            If foundsheet.Name = CStr(cs.Cells(foundrows, 1).Value) Then
                'found correct sheet, now find col_y last number
                foundsheet_bool = True
                lasty = foundsheet.Cells(foundsheet.Rows.Count, "y").End(xlUp).Row
                cs.Cells(foundrows, cols).Value = foundsheet.Cells(lasty, 25).Value
            End If
        Next
        If Not (foundsheet_bool) Then
            
                If (InStr(cs.Cells(lastrow + 1, cols).Value, foundsheet.Name) <= 0) Then
                    If cs.Cells(lastrow + 1, cols).Value = "" Then
                        cs.Cells(lastrow + 1, cols).NumberFormat = "@"
                        cs.Cells(lastrow + 1, cols).Value = foundsheet.Name
                        
                    Else
                        If stringcounter = 1 Then
                            cs.Cells(lastrow + 1, cols).Value = CStr(cs.Cells(lastrow + 1, cols).Value) + "," + foundsheet.Name
            
                        Else
                            cs.Cells(lastrow + 1, cols).Value = cs.Cells(lastrow + 1, cols).Value + "," + foundsheet.Name

                        End If
                        
                    End If
                End If
                stringcounter = stringcounter + 1
        End If
    Next
    nw.Close
    
Next

Application.ScreenUpdating = True
End Sub

Open in new window


Edit: Oh wait, I think I know what you mean, but I need more time for that. So you want to go through sheet you will ever encounter, right?
It means all sheets will have a column Y with the last line you want? Because it was quite misleading then (cell A10 and A11 in Accounts). You should have started out as saying cell A10 till Axx will be used for all sheets encountered.
Update tomorrow I hope, because that made it quite a bit more complicated.
0
 

Author Comment

by:bikeski
ID: 40519177
Hi Kimputer,
Sorry for the delay in my reply, the holidays have been quite busy. I haven't had a chance to try the macro yet but wanted to get your a response.

Yes, I would like the macro to add the account number/worksheet name in DataFile1.xlsx not yet listed in Accounts.xlsx cells A10 thru Axx. I hope that makes sense.

Thx
0
 

Author Comment

by:bikeski
ID: 40519857
Kimputer,, your code works. I see how you're listing the accounts not found at the bottom.  Are you able to finish the insert row for non listed accounts? I can always start another thread. Thx
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 40521605
Actually, the change from current code to the one you need (automatically insert the list) will take a lot of extra code. I will need a bit more time to think about it and then code it.
0
 
LVL 35

Accepted Solution

by:
Kimputer earned 500 total points
ID: 40521859
Updated the code. Your first example had A10 and A11 prefilled with information. This is NOT necessary anymore (of course, you can, but it doesn't seems logical):

Sub test()

Application.ScreenUpdating = False
Dim cw, nw As Workbook
Dim cs, ns As Worksheet

Set cw = ActiveWorkbook
Set cs = cw.Sheets(1)

lastrow = cs.Cells(cs.Rows.Count, "A").End(xlUp).Row 'important for sheet names check
If lastrow < 10 Then
    lastrow = 10
End If
lastcol = cs.Cells(7, cs.Columns.Count).End(xlToLeft).Column 'to check how many files to open and read

    addedcounter = 0
For cols = 2 To lastcol Step 1
    'open file

    Set nw = Workbooks.Add(cs.Cells(7, cols).Value & "\" & cs.Cells(8, cols).Value)
    For Each foundsheet In nw.Sheets
        foundsheet_bool = False
        For foundrows = 10 To (lastrow + addedcounter) Step 1
            If foundsheet.Name = CStr(cs.Cells(foundrows, 1).Value) Then
                'found correct sheet, now find col_y last number
                foundsheet_bool = True
                lasty = foundsheet.Cells(foundsheet.Rows.Count, "y").End(xlUp).Row
                cs.Cells(foundrows, cols).Value = foundsheet.Cells(lasty, 25).Value
            End If
        Next
        
        If Not (foundsheet_bool) Then
                
                cs.Cells(lastrow + addedcounter, 1).Value = foundsheet.Name
                For foundrows = 10 To (lastrow + addedcounter) Step 1
                    If foundsheet.Name = CStr(cs.Cells(foundrows, 1).Value) Then
                        'found correct sheet, now find col_y last number
                        lasty = foundsheet.Cells(foundsheet.Rows.Count, "y").End(xlUp).Row
                        cs.Cells(foundrows, cols).Value = foundsheet.Cells(lasty, 25).Value
                    End If
                Next
                addedcounter = addedcounter + 1
        End If
    Next
    nw.Close
    
Next

Application.ScreenUpdating = True
End Sub

Open in new window

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Closing Comment

by:bikeski
ID: 40522623
Thanks for completing the project, it works well.
0
 

Author Comment

by:bikeski
ID: 40522849
Hello Kimputer,

FYI, I've posted a new question with some tweaks for this solution. After I worked with the file a bit, I realized it would be better to select the files/columns in Accounts.xlsx instead of re-importing every time. Also, I'd like to sort the data to keep the account numbers organized.

The new questions is at:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28588137.html
0
 

Author Comment

by:bikeski
ID: 40524806
Hi Kimputer,

I'm finding that some of my Data files are generating the balance total in column S instead of column Y.  I've tried changing the code to read:

lasty = foundsheet.Cells(foundsheet.Rows.Count, "s").End(xlUp).Row

but that's not working. Any suggestions? It might be best to test which column has the data and then calculate from there.

Thx
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 40525326
I think I will integrate this in that follow up question. But again, I think I need more time.
0
 

Author Comment

by:bikeski
ID: 40526070
Sounds good, Thx.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now