Open 1 WorkBook with 2 WorkSheets from TextBoxes

[Andresleo47]

Hi Community! I am trying to open one Workbook with the 2 TimeSheets in the attached TextBoxes.

This is the code I have for now, but it's giving me an error:

Sub CommandButton1_Click()

Workbooks.Add.SaveAs Filename:="ThisWeek.xlsx"
ActiveWorkbook.Worksheets.Add Count:=1

With Sheets("Sheet1")
Workbooks.Open .TextBox1.Text
End With
With Sheets("Sheet2")
Workbooks.Open .TextBox2.Text
End With
End Sub


Any ideas would be appreciated!!

 

[Andresleo47]

Thanks Johnny. With this newly created Active Workbook, I would like to get the data stored in both paths from the TextBoxes in 2 different sheets. Not sure if I'm making sense. Each TextBox leads to different WorkBooks (reports that I download), and I want to build a new file called "ThisWeek" where I can put the content of one TextBox in one Sheet and the content of the second Textbox in another sheet.

Let me know if you have questions, because I'm not even sure if it's clear

Thanks,

Regards,

 

[johnnyL]

If my math is correct, you want to use one workbook to create a new workbook, then open two more workbooks and copy a sheet from each one of those two workbooks, into the new workbook.

 

[Andresleo47]

The first Workbook is the Macro, standard. I put it in a different file because if I need to send it over to other people, it would work. Screenshot attached and another post will follow after this one...

 

[Andresleo47]

When I hit the CommandButton called "Open Working File", a new Excel File (Workbook) must be opened with 2 Worksheets (Those new Worksheets would be the files that are in the TextBoxes).

 

[Andresleo47]

After that, I need to do some manipulation, Pivot Tables and stuff, but I think that's easier. Hope that makes sense?

Let me know if you have any questions.

Andres

 

[Andresleo47]

In other words, how do I open a WorkBook from a path that is clustered in a TextBox?

 

[johnnyL]

In other words, how do I open a WorkBook from a path that is clustered in a TextBox?

Example: If Sheet1 is the sheet name that contains the textbox, and the textbox that you are interested in is TextBox1

Application.Workbooks.Open Sheets("Sheet1").TextBox1.Value

 

[Andresleo47]

Hi ,thanks for replying.

So, TextBox1 and TextBox2 have 2 different paths, both of them lead to different Excel files. When I download those 2 reports from the server, they come already with names in the Sheets. The name of the sheet is "report" for both files. However, I don't want to affect any of those 2 files, that's why I'm trying to copy-paste them in a new file: "ThisWeek".

I wish I could send a Doc file instead of an image.

Thanks so much!

Regards,

 

[johnnyL]

I think the following will do what I think you are wanting:

Sub CommandButton1_Click()
'
    Dim ClosedSourceWorkbook    As Workbook
'
    Workbooks.Add.SaveAs Filename:="ThisWeek.xlsx"                                          ' Create/Name/save New Workbook
'
    ThisWorkbook.Activate                                                                   ' Reactivate this workbook
'
    Application.ScreenUpdating = False                                                      ' Turn ScreenUpdating off
'
    Set ClosedSourceWorkbook = Workbooks.Open(Sheets("Sheet1").TextBox2.Value)              ' Get value from TextBox to open the source file
    ClosedSourceWorkbook.Sheets("report").Copy Before:=Workbooks("ThisWeek.xlsx").Sheets(1) ' Copy sheet to beginning of the newly created workbook
    ClosedSourceWorkbook.Close SaveChanges:=False                                           ' Close the source workbook
'
    ThisWorkbook.Activate                                                                   ' Reactivate this workbook
'
    Set ClosedSourceWorkbook = Workbooks.Open(Sheets("Sheet1").TextBox1.Value)              ' Get value from TextBox to open the source file
    ClosedSourceWorkbook.Sheets("report").Copy Before:=Workbooks("ThisWeek.xlsx").Sheets(1) ' Copy sheet to beginning of the newly created workbook
    ClosedSourceWorkbook.Close SaveChanges:=False                                           ' Close the source workbook
'
    Application.DisplayAlerts = False                                                       ' Turn off DisplayAlerts to avoid sheet deletion warning
    Sheets("Sheet1").Delete                                                                 ' Delete Sheet1 from the newly created workbook
    Application.DisplayAlerts = True                                                        ' Turn DisplayAlerts back on
'
    ActiveWorkbook.Close True                                                               ' Save newly created file and close
'
    Application.ScreenUpdating = True                                                       ' Turn ScreenUpdating back on
End Sub

 

[Andresleo47]

I think the following will do what I think you are wanting:

Sub CommandButton1_Click()
'
    Dim ClosedSourceWorkbook    As Workbook
'
    Workbooks.Add.SaveAs Filename:="ThisWeek.xlsx"                                          ' Create/Name/save New Workbook
'
    ThisWorkbook.Activate                                                                   ' Reactivate this workbook
'
    Application.ScreenUpdating = False                                                      ' Turn ScreenUpdating off
'
    Set ClosedSourceWorkbook = Workbooks.Open(Sheets("Sheet1").TextBox2.Value)              ' Get value from TextBox to open the source file
    ClosedSourceWorkbook.Sheets("report").Copy Before:=Workbooks("ThisWeek.xlsx").Sheets(1) ' Copy sheet to beginning of the newly created workbook
    ClosedSourceWorkbook.Close SaveChanges:=False                                           ' Close the source workbook
'
    ThisWorkbook.Activate                                                                   ' Reactivate this workbook
'
    Set ClosedSourceWorkbook = Workbooks.Open(Sheets("Sheet1").TextBox1.Value)              ' Get value from TextBox to open the source file
    ClosedSourceWorkbook.Sheets("report").Copy Before:=Workbooks("ThisWeek.xlsx").Sheets(1) ' Copy sheet to beginning of the newly created workbook
    ClosedSourceWorkbook.Close SaveChanges:=False                                           ' Close the source workbook
'
    Application.DisplayAlerts = False                                                       ' Turn off DisplayAlerts to avoid sheet deletion warning
    Sheets("Sheet1").Delete                                                                 ' Delete Sheet1 from the newly created workbook
    Application.DisplayAlerts = True                                                        ' Turn DisplayAlerts back on
'
    ActiveWorkbook.Close True                                                               ' Save newly created file and close
'
    Application.ScreenUpdating = True                                                       ' Turn ScreenUpdating back on
End Sub

Hi, thanks so much!!

I tried it, but this comes up (See attached).

Any thoughts? Maybe it's a server issue?

Not sure! Thanks for your efforts and help!