Jared_Jones_23

New Member
Joined
Jun 24, 2011
Messages
34
Hello I have written a macro that when run, it opens a local documents folder and lets you select any number of files, the files are then copied and pasted into the original sheet. Now I am trying to pull files from an online sharepoint using the same macro. I am having trouble referencing the sharepoint to be the folder that is opened to select files. Here is my code and the highlighted part is where my trouble is. Any suggestions are appreciated thank you.

Sub Data()
Dim filenames As Variant
Dim macroname As String
Dim fnam, cse As String
Dim wbOpen As Workbook
Dim i, src_frow, src_lrow, tgt_frow, tgt_lrow, src_lcol, delcnt As Long
src_frow = 20
tgt_frow = 20
src_lcol = 138
cse = Range("D4").Value
delcnt = 0
macroname = "New SAM MACRO_COMBINE_3.xlsm"
fnam = ActiveWorkbook.Name
'If fnam <> macroname Then
' MsgBox "Please open " & macroname
' GoTo endout
'End If

Application.ScreenUpdating = False

filenames = Application.FollowHyperlink("http://my.website.my.shared Documents", , , True)
counter = 1
While counter <= UBound(filenames) ' ubound determines array size

Application.EnableEvents = False
Set wbOpen = Workbooks.Open(filenames(counter))
Application.EnableEvents = True

Sheets("Data").Select
Range("A20").Select
Selection.AutoFilter
ActiveSheet.rows.EntireRow.Hidden = False
ActiveSheet.Columns.EntireColumn.Hidden = False

If cse = "Total" Then
Call delbrows1(src_frow)
Else
Call delbrows2(src_frow, cse, delcnt)
End If

src_lrow = LastRowIndex(ActiveSheet, "B") 'LastRowIndex(ActiveSheet, 2)
Range(Cells(src_frow, 1), Cells(src_lrow, src_lcol)).Copy
Windows(macroname).Activate
tgt_lrow = src_lrow - src_frow + tgt_frow
Range(Cells(tgt_frow, 1), Cells(tgt_lrow, src_lcol)).PasteSpecial
Application.CutCopyMode = False
wbOpen.Close False
tgt_frow = tgt_lrow + 1
counter = counter + 1
cwrite = counter - 1
Wend

Call mod_cse(src_frow, cse, filenames, cwrite)
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs "SAM_" & cse & "_" & Format(Now, "yyyymmdd") & ".xlsx", FileFormat:=51
Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox ("File saved as: " & ActiveWorkbook.Name _
& vbCr & vbCr)
Sheets("Data").ListObjects("Table3").Range.AutoFilter Field:=2, Criteria1:="<>"
endout:
End Sub
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Watch MrExcel Video

Forum statistics

Threads
1,123,176
Messages
5,600,146
Members
414,365
Latest member
UUR

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top