Open new workbook, make active, perform save as

[LlebKcir]

Hello, I have a large process that I am attempting to break down into smaller steps.

workbook 1 call Master
workbook 2 call Table

From the Master I want to open Table, make that workbook active, eventually populate with data from Master, and do a save as local. These files are on a ShareNow drive. I have been successful at getting the Table to open, I even tested by hiding one worksheet in Table, making it visible and selecting A1 before attempting to do the save as.

Thats when everything falls apart. Even though I have Table set as the active workbook and Data as the active selected worksheet, Save As tries to save the Master, not the Table.

Sub OpenTable()
Dim wb As Workbook      ' Workbook varible
Dim ws As Worksheet     ' Worksheet varible
Dim sFName As String    ' String used as part of the Save As process

Set objExcel = CreateObject("excel.application")    ' Opens MS Excel
' Opens Tabel from ShareNow, currently hardcoded to test folder
Set wb = objExcel.Workbooks.Open("https://sharenow..../TESTING/Table-TEST-formulas.xlsx")

wb.Application.Visible = True           ' Makes the Table spreadsheet visible
Set ws = wb.Worksheets("Data")          ' Test to see how to activate a specific worksheet in attempt to perform save as function on Table
ws.Visible = xlSheetVisible             ' Part of the test for save as
'Set ws = wb.Worksheets("Table")     ' Correct worksheet to activate
ws.Activate                             ' Marks the correct workbook and worksheet to active status
ws.Range("a1").Select                   ' Test to confirm Table workbook and Data worksheet had been properly activated prior to save as

' Start Save As process
sFName = Application.GetSaveAsFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx, Macro Enabled Workbook" & "(*.xlsm), *xlsm")

    If sFName <> "False" Then
        ws.SaveAs sFName, 56
    End If

End Sub

Thank you in advance for the help and guidance.

 

[Norie]

Why are you creating another instance of Excel to open the workbook 'Table-TEST-formula.xlsx'?

 

[LlebKcir]

Lack of knowledge. My google foo seems to have failed me. All indications I found for opening another workbook via sharenow indicated the user the CreateObject("excel.application")

Is that part of the problem?

 

[LlebKcir]

Norie, thank you for that question. After i started playing with a few other settings inside of Table it became clear why you asked. I commented out that line, edited my set wb line to read:

set wb = Workbooks.Open("path.to.file")

Now the save as is functioning properly.

Thank you. Is there a way to mark threads solved in this place?