Solved

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

Posted on 2014-12-20
10
123 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

758 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

20 Experts available now in Live!

Get 1:1 Help Now