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]

Hi, any help here?

 

[Andresleo47]

Adding more details, I would like a New Excel file to be opened with 2 sheets (The 2 sheets should have the information coming from each TextBox), and I would like it to be saved in "MyDocuments".

Kindly advise.

Thanks,

 

[johnnyL]

Your code is trying to read the information from the new workbook. Since there are no textboxes in the newly created workbook, that is why it is not working.

When you create a new workbook, that new workbook is the active workbook.

 

[Andresleo47]

Thanks Johnny, so when I hit the CommandButton, an Excel file is created and saved as ThisWeek.xls, but nothing else happens and then is when I receive the error.

Any advise?

Regards,

Andres

 

[Andresleo47]

The error is shown here (Attached)

 

[johnnyL]

Thanks Johnny, so when I hit the CommandButton, an Excel file is created and saved as ThisWeek.xls, but nothing else happens and then is when I receive the error.

Any advise?

See my previous post for the reason why it is not working.

 

[Andresleo47]

Gotcha, thanks. Then this statement should be in the first modules?

xlWorkbook1 = xlApp.Workbooks.Open(TextBox1.Text)

How can I connect both workbooks (The one that I want to create and the one with the macro)?

Thanks so much for your help.

Regards,

Andres

 

[Andresleo47]

And this is actually the error.

 

[johnnyL]

Your first 1st line of code creates/names/saves a workbook.
Your 2nd line of code adds a sheet to the new workbook.

What exactly are you wanting to do with this newly created and Active workbook?