maneuvering btwn workbooks with vba

[Mr_Ragweed2]

Struggling with "activeworkbook" vba. I have a spreadsheet with a button (who doesn't, right?). This button is supposed to take all of the .xlsx files, recognize the name, and save as pdf's to various destination folders on sharepoint/teams based on that name. Most of that actually happens. Pathways and loop is fine. What i realized though is that is is actually just taking a pdf of the workbook that has the button. Most assuredly it is through my own error. My question is how do i make the file that it finds the new activeworkbook so my SaveAs takes the correct pdf snapshot? Important to note that all of the files are the same folder as the spreadsheet with the button. (button sheet is, and will always be the only .xlsm file.) My code is below (edited for length due to loop):

Option Explicit

Private Sub CommandButton1_Click()

    Dim sourceFolderPath As String
    Dim oldName As String
    Dim newName As String
    Dim wb As Workbook
    
    Dim FSO As Object
    Dim SourceFolder As Object
    Dim File As Object
    
    Application.ScreenUpdating = False
    
    Dim MyName As String
    MyName = Environ$("Username")
        
    sourceFolderPath = "C:\Users\" & MyName & "\OneDrive - MyCompany\Desktop\Propane Forms"
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set SourceFolder = FSO.GetFolder(sourceFolderPath)
    
    '12-21 8-18am this mostly works. files go to correct folders and are deleted. "ZSync" is left alone.
    'only issue is it is making a pdf of the zsync file prolly because it is listed as the active workbook
    
    On Error Resume Next
    
    For Each File In SourceFolder.Files
    
        oldName = File.Name
    
    newName = Left(oldName, Len(oldName) - 5)
    
    If oldName Like "*ZSync*.xlsm" Then
    Exit Sub
    
    ElseIf oldName Like "*SWO*" & ".xlsx" Then
        Set wb = Workbooks(oldName).Open ' this line and the next are my attempt to force the found file to be the activeworkbook, but obviously does not work
        Range("A1").Select
        ActiveWorkbook.SaveAs fileName:= _
            "https://abcd.sharepoint.com/teams/mycompany/Shared%20Documents/General/Crystal,%20Mary,%20Anna/Propane%20Service%20Work%20Orders" _
             & "/" & newName & ".pdf"
            ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF
        Kill "C:\Users\" & MyName & "\OneDrive - mycompany\Desktop\Propane Forms\" & File.Name
        
        ElseIf oldName Like "*TMS*" & ".xlsx" Then
        ActiveWorkbook.SaveAs fileName:= _
            "https://abcd.sharepoint.com/teams/mycompany/Shared%20Documents/General/Crystal,%20Mary,%20Anna/Tank%20Movement%20Sheet/" _
            & newName & ".pdf"
            ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF
        Kill "C:\Users\" & MyName & "\OneDrive - mycompany\Desktop\Propane Forms\" & File.Name
        
    End If
 
    Next File
    
    Set SourceFolder = Nothing
    Set FSO = Nothing
    
End Sub

As an aside, and will probably be another thread. The pdf's won't open in sharepoint. I have to open them from my documents folder on my desktop. Any thoughts here would be appreciated as well.

 

[Skyybot]

It is good practice to try not to use ActiveWorkbook/ActiveSheet in your code. Especially when you are working with more than one Workbook. If your Module is in the Workbook that you want to manipulate then try something like

Dim origWB as Workbook
Set origWB = ThisWorkbook

 

[Mr_Ragweed2]

I want to manipulate all of the other workbooks, not the one with the module in it. And i won't know the names of any of the workbooks until the files are found.

Using your method, "ThisWokbook" is where the module is and therefore Activeworkbook would be any file that is selected? Just trying to make sure i understand. Thank you for your assistance on this.

 

[Skyybot]

Set wb = Workbooks(oldName).Open

Needs to be full Path of File not just FileName (unless you use ChDir). Once it is found and opened your wb variable is already set, so

wb.SaveAs

should work.

 

[Mr_Ragweed2]

Sorry for the delay with the holiday. Hope yours was well.
So i am still struggling with this. Here are my changes:

 ElseIf oldName Like "*SWO*" & ".xlsx" Then
        Set wb = "C:\Users\" & MyName & "\OneDrive - mycompany Inc\Desktop\Propane Forms\" & File.Name ' full path of file like you said
        wb.SaveAs fileName:= _          
            "https://abcd.sharepoint.com/teams/my company/Shared%20Documents/General/Crystal,%20Mary,%20Anna/Propane%20Service%20Work%20Orders" _
             & "/" & newName & ".pdf"      'changed nothing here
            ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF

it is still saving a pdf of the wrong wb and now it is not even saving it to sharepoint/teams just my local C: documents folder

 

[Mr_Ragweed2]

So i finally read more about what you were saying and came up with this code below and it works.

ElseIf oldName Like "*SWO*" & ".xlsx" Then
        Set wb2 = Workbooks.Open(File)
        wb2.SaveAs fileName:= _
            "https://abcd.sharepoint.com/teams/mycompany/Shared%20Documents/General/Crystal,%20Mary,%20Anna/Propane%20Service%20Work%20Orders" _
             & "/" & newName & ".pdf"
            wb2.ExportAsFixedFormat Type:=xlTypePDF

Thank you for pointing me in the right direction!