Excel Worksheet to Auto fill using Macro

[Joey86]

Hi,

I'm fairly new to using VBA in excel, I have a table in worksheet owssvr(1) this is a data dump from the SharePoint connection I put from a custom list and I will be running the macro from a command button on the 'Main Menu' worksheet. I'm just needing the macro to do the following:

1) Refresh the data connection to get the latest data
2) In worksheet 'owssvr(1)' column "BL" auto fill down (so basically row BL2 = 1, BL3 = 2, BL4 = 3) but I only want it to fill the rows that have data because there will be new rows of data everytime the data is refreshed.

Thanks for anyone's help, really appreciate it.
Cheers

 

[Joe4]

Welcome to the Board!

Regarding question 1, I haven't worked with Sharepoint, but this links looks like it might contain what you need: Dan's SharePoint Blog: How to Link Your SharePoint List to Excel!

Regarding question two, it looks like you are trying to populate column BL with a counter that is one less than the current row number (starts counting from 1 on row 2). Assuming when you say "Autofill", you are looking in column BK to determine how far down to go, this VBA code should do what you want:

Sub MyColumnFill()


    Dim myLastBKRow As Long
    Dim myLastBLRow As Long
    
'   Find last row in column BK
    myLastBKRow = Cells(Rows.Count, "BK").End(xlUp).Row
    
'   Find last row in column BL
    myLastBLRow = Cells(Rows.Count, "BL").End(xlUp).Row
    
'   Fill missing row in column BL with counter one less than row number
    If myLastBKRow > myLastBLRow Then
        Range(Cells(myLastBLRow + 1, "BL"), Cells(myLastBKRow, "BL")).Formula = "=Row() - 1"
    End If
    
End Sub

Hope that helps!

 

[Joey86]

Thanks for the quick response Joe, really appreciate it and thanks hopefully joining the forum i'll learn a lot more.

Well basically with the Sharepoint list I can explain it a bit better, its basically a custom list of data, in the options of the list I can export that data and column headers into excel as a table (owssvr(1)) which also has a direct connection to the list id and view guid in the excel data connections. So when the workbook is refreshed it retains the data thats there and pulls down the latest information from the list into the table, and you can use queries in excel like =countifs(Table_owssvr__1[[#All],[Customer]],'Results'!B4.

With the VBA I'm not actually needing it to autofill column 'BK', I'm just needing it to fill the formula down to any empty fields in the table when I refresh and get the latest info as shown below, it will need to be dynamic because there will always be new data coming in. 'RowNum' is BL

EDIT: Also am I able to attach the VBA code to a button so that when people hit it to refresh the data, not only does it pull down the latest info into the table but also adds it's row number?

Thanks

 

[Joey86]

I also tried the VBA code although its missing the blank cells and only going to the last row and inserting its formula in BL and then adding a new row below

 

[Joe4]

With the <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> I'm not actually needing it to autofill column 'BK', I'm just needing it to fill the formula down to any empty fields in the table when I refresh and get the latest info as shown below, it will need to be dynamic because there will always be new data coming in. 'RowNum' is BL

That's pretty much what my code does!

Let's make sure we are on the same page here. Is this what you want to do?
- "Autofill" column BL with row number (which is starts counting with number 1 in Excel row #2)

If every row is not being populated, then I think that you may have some "gaps" in your data, or some rows in column BL are not actually blank (maybe they have a space in them). WE can get around all that if we just re-number all rows in column BL every single time (shouldn't hurt anything).

It also looks like your column BL is formatted as Text, so all the formulas are being pasted in as Text instead of formulas. We can get around that too. Here is updated code, along with the data refresh line for Sharepoint that they showed you how to do in that link I provided for you in my first post:

Sub MyColumnFill()

    Dim myLastBKRow As Long
    
'   Refresh Sharepoint data
    ActiveWorkbook.RefreshAll


'   Find last row in column BK
    myLastBKRow = Cells(Rows.Count, "BK").End(xlUp).Row
    
'   Reformat column BL to be numeric
    Columns("BL:BL").NumberFormat = "0"
    
'   Fill missing row in column BL with counter one less than row number
    If myLastBKRow > myLastBLRow Then
        Range(Cells(2, "BL"), Cells(myLastBKRow, "BL")).Formula = "=Row() - 1"
    End If
    
End Sub

 

[Joey86]

That works perfect, thanks so much Joe, really appreciate your help on this. One last question, on another sheet 'Main Menu' I've added a button which has the refresh option (thanks to the link you posted) to refresh the data from sharepoint, how do I add your VBA to that button so while the data refreshes in (owssvr(1)) worksheet and puts in new rows of information that when its completed it runs your VBA to add the row numbers.

 

[Joe4]

Just add the following line of code to Select/Activate the sheet you wish to apply it to. Add it just before the "Refresh" line.

'   Refresh Sharepoint data 
[B]    Sheets("[COLOR=#333333]owssvr(1)").Activate[/COLOR][/B]
    ActiveWorkbook.RefreshAll

 

[Joey86]

Oh no not the sharepoint refresh part the Sub MyColumnFill() part, I'm needing to add that to the button (Commandbutton1) so that when clicked it refreshes the data and does the column fill part.

Thanks

 

[Joe4]

I am afraid I am not sure what you are asking. Do you not know how to assign a macro to a Command Button?

 

[Joey86]

Well sort of, I've tried a couple of times using (Private Sub Commandbutton1_Click()) but you have (Sub MyColumnFill()) and when I add MyColumnFill() it doesn't seem to work