vba to select all pdf files from a folder and save to Onedrive

[Mr_Ragweed2]

Hello again. I have searched this forum and cant find help on my topic. I have a file on a users desktop that will contain a number of pdf files. These files will save to specific folders on Teams ( i have those pathways and have done it with .xlsx and .xlsm files before.) The files have a prefix in each filename to specify the destination. ex) SWO, NCI, LCF, PSR, & TMS. So I'm imagining looping through all the files and "If SWO...... then save to that pathway", etc.
These files were originally excel files that were saved as pdfs. Maybe it would be easier to do the pdf conversion in the pathway save to Teams? Either way, how do i loop thru the files and make this come true? The command will be executed from a command button on a dedicated spreadsheet that will always be in the folder.

Any help is greatly appreciated. I hate that i don't have any sample code to supply ay this point. I have done loops thru worksheets before but never through a folder and never looking for pdf's - the wording escapes me.

 

[Mr_Ragweed2]

Ok, i see some views and no responses - That's probably on me for asking a bad question and not having an example. Below is my code attempt taken from the vba and macros book. I'm sure there are plenty of holes in my attempt to adapt it. The error is in calling the the second sub "recurse SubFolders" bit. I have tried it at the end of my code(as shown) and in a seprate module. This current code that you see is on the command button on the sheet. Error is: "Run Time error '76 - Path not found".

Private Sub CommandButton1_Click()

'Sync all forms (pdfs) to Teams except the "Sync forms to teams  - do not delete this".xlsm worksheet
' all files will be in pdf form, so the excel form can stay

'from "VBA and Macros Microsoft Excel 2013" book pgs 90-92
Dim fso As Object
Dim strName As String
Dim strArr(1 To 1048576, 1 To 1) As String, i As Long

Const strDir As String = "C:\Propane Forms\"

strName = Dir$(strDir & "*.pdf")
Do While strName <> vbNullString
    i = i + 1
    strArr(1, 1) = strDir & strName
    strName = Dir$()
Loop
Set fso = CreateObject("Scripting.FileSystemObject")
Call recurseSubFolders(fso.getFolder(strDir), strArr(), i)
Set fso = Nothing

For Each File In strArr
    If strName = ("SWO " & "" & "" & "") Then
    File.SaveAs Filename:= _
    "https//chsinc.sharepoint.com/teams/CHSHighPlainsEnergy/Shared20%Documents/General/Crystal,%20Mary,%20Anna/Propane%20Work%20Orders/" _
    & ".pdf", CreateBackup:=False

ElseIf strName = ("TMS " & "" & "" & "") Then
  File.SaveAs Filename:= _
  "https//chsinc.sharepoint.com/teams/CHSHighPlainsEnergy/Shared20%Documents/General/Crystal,%20Mary,%20Anna/Tank%20Movement%20Sheet/" _
    & ".pdf", CreateBackup:=False

End If

Next File

End Sub


Private Sub recurseSubFolders(ByRef Folder As Object, ByRef strArr() As String, ByRef i As Long)

Dim subFolder As Object
Dim strName As String
For Each subFolder In Folder.SubFolders
    strName = Dir$(subFolder.Path & "*.pdf")
    Do While strName <> vbNullString
    i = i + 1
    strArr(i, 1) = subFolder.Path & strName
    strName = Dir$()
Loop
Call recurseSubFolders(subFolder, strArr(), i)
Next


End Sub

Sorry for not being very savvy on this. Thank you for any help/guidance you provide - even if it is just to tell me im an idiot

 

[Mr_Ragweed2]

Ok i'm dumbing it down for myself. At this point i have cut out the loop and am just trying to move any/all files to the same destination but i keep getting a "path not found" error on the last line (green part). code below:

Private Sub CommandButton_Click()

    Dim fso As Object
    Dim fileLocation As String
    Dim Destination As String
    
    Dim MyName As String
    MyName = Environ$("Username")
    
    Set fso = CreateObject("Scripting.FileSystemObject")

    fileLocation = "C:\Users\" & MyName & "\OneDrive - XXXX\Desktop\Propane Forms" & "\" & "*.PDF"
    [COLOR=rgb(26, 188, 156)]fso.MoveFile Source:=fileLocation, Destination:="https://xxxx.sharepoint.com/teams/MyCompany/Shared20%Documents/General/Crystal,%20Mary,%20Anna/Propane%20Service%20Work%20Orders/"[/COLOR]

End Sub

I really really need help on this. Thank you in advance!

 

[Mr_Ragweed2]

I am way out of my league here on something that seems so simple in my mind. I have spent 5 days looking for code examples that i might be able to adapt and just cant understand most of what i am reading. Here is my attempt in laymans terms. Tell me what you think/point me in the right direction and i promise i will do the research and try to do it on my own.
I need to take this:

and save it to appropriate folders on sharepoint. (EX: SWO goes to a different folder than TMS). These files are created by users when they are in the field and do not have internet. For simplicity i put them all in the same folder on their desktop. I have code that identifies the user and does the save. This is part of that code (Let's call it Code A):

Dim MyName As String
    MyName = Environ$("Username")
    
    ChDir "C:\Users\" & MyName & "\OneDrive - MyCompany Inc\Desktop\Propane Forms" 'from excel forum
    
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\" & MyName & "\OneDrive - MyCompany Inc\Desktop\Propane Forms" & "\" & savename & ".PDF" 
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF        
    ActiveWindow.Close

Now in the part i will call Code B from another project, i can save files directly to our Teams/sharepoint site. That part is listed below:

Activeworkbook.SaveAs Filename:= _
"https://my company.sharepoint.com/teams/Mycompany/Shared%20Documents/general/Crystal,%20Mary,%20Anna/SWO%completed/" _
& "\" & newfile & Format(Now, "dd-mmm-yyy" & ".xlsx" FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

I have never written code dealing with filescripting but it seems like thats what i need? (identify the files in a folder based on criteria ( "WSO" & "" & "" & "" & ".pdf") and then select the file and then save it to a newpath or destpath or whatever i name it as a string right? And the folder they are currently in would be the source path? and then whole thing has to be a loop. So something like For Each file that is WSO then save here, Else IF file is TMS Then save over there. And it's an unkown number of files everytime.)

I promise i am trying, i am just really really frustrated and apologize if i come across that way. Thanks for reading my rant
The other frustrating part is that i probably posted the question bad the first time, bumped it once myself, and now that i'm being hopefully more clear it already shows up as having responses so i'm less likely to get clicks. And there's no other way to repost this without it being a duplicate thread and breaking the rules haha.