Macro to grab data from file located on sharepoint

[1nk3d]

I need some help with a macro here. Currently I have a workbook that will copy data from several workbooks on a shared drive. The macro works fine, however it is designed for multiple units to utilize, so therefore I use some array formulas which really slow things down. What I would like to do is instead of having the array formulas to filter the data, have the macro copy the data from the books on shared drive and paste it over, depending on selection. Which would require a report not being able to be ran unless a unit is selected. Any advice, suggestions?

 

[Niteshnitesh]

Re: Help with macro to grab data from file located on sharepoint

Could you please provide your code so we can suggest you better.

 

[1nk3d]

Re: Help with macro to grab data from file located on sharepoint

Here is what I have for part of the code. I redacted the urls. This code repeats itself several times, as it pulls data from another workbook, the code and format is the same, the data is different.

Public Sub OpenPrevWeek()
Dim wkbMyWorkbook As Workbook
Dim wkbWebWorkbook As Workbook
Dim wksWebWorkSheet As Worksheet
Set wkbMyWorkbook = ActiveWorkbook
Workbooks.Open ("url here")

Set wkbWebWorkbook = ActiveWorkbook
Set wksWebWorkSheet = ActiveSheet
wksWebWorkSheet.Copy After:=wkbMyWorkbook.Sheets(Sheets.Count)
wkbMyWorkbook.Sheets(ActiveSheet.Name).Name = "PW"
wkbMyWorkbook.Activate
wkbWebWorkbook.Close
PW
End Sub
Sub PW()
'
' PW Macro
'
'
    Sheets("PW").Select
    Sheets("pwoutput").Visible = True
    Sheets("PW").Select
    Columns("B:B").EntireColumn.AutoFit
    Columns("A:A").EntireColumn.AutoFit
    Range("A3:BE25184").Select
    Selection.Copy
    Sheets("pwoutput").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("pwoutput").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("PW").Select
    Application.CutCopyMode = False
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
    Application.GoTo Sheets("Fri").Range("A1"), True
    OpenPWhoc
End Sub

 

[vmjan02]

Re: Help with macro to grab data from file located on sharepoint

Try this code, I hope this will solve the problem.

Private Sub CommandButton1_Click()
Sheets("Sheet1").Select
Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
With FldrPicker
.Title = "Select A Target Folder"
.AllowMultiSelect = False
If .Show <> -1 Then GoTo NextCode
myPath = .SelectedItems(1) & ""
End With




NextCode:
myPath = myPath
If myPath = "" Then GoTo ResetSettings
myExtension = "*.xls" ' Can change the file type
myFile = Dir(myPath & myExtension)
Do While myFile <> ""
Set wb = Workbooks.Open(Filename:=myPath & myFile)




With Workbooks(myFile)
With .Range("a2:BZ" & .Range("B" & .Rows.Count).End(xlUp).Row)
.Copy
End With
End With




wb.Close SaveChanges:=False
myFile = Dir
Loop
MsgBox "Task Complete!"
ResetSettings:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True




End Sub

 

[1nk3d]

Re: Help with macro to grab data from file located on sharepoint

I forgot to add that the only data I want to copy over would be data that matches column B on the data set to cell B5 on the current workbook.