Open 1 WorkBook with 2 WorkSheets from TextBoxes

Andresleo47

Board Regular
Joined
Oct 29, 2008
Messages
132
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!!
 

Attachments

  • Capture5.PNG
    Capture5.PNG
    22.4 KB · Views: 10
You have to change the sheet name in the two lines that refer to the Text Box.

VBA Code:
    Set ClosedSourceWorkbook = Workbooks.Open(Sheets("Sheet1").TextBox2.Value)              ' Get value from TextBox to open the source file

and
VBA Code:
    Set ClosedSourceWorkbook = Workbooks.Open(Sheets("Sheet1").TextBox1.Value)              ' Get value from TextBox to open the source file

So change the 'Sheet1' in both of those lines to the sheet name that contains your two text boxes.
 
Upvote 0

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Hi, thanks. I tried to change the code's Sheet1 to the name that actually has the Sheet1 on the report (Attached) .

File Name = Spend by Contractor
File Path = The one on the second TextBox (For this example)
Sheet Name = report

But I still get the error :(
 

Attachments

  • Capture14.PNG
    Capture14.PNG
    11.6 KB · Views: 4
  • Capture15.PNG
    Capture15.PNG
    172.9 KB · Views: 3
  • Capture3.PNG
    Capture3.PNG
    18.1 KB · Views: 2
Upvote 0
what is the name of the sheet that your text boxes/open working file button are on?
 
Upvote 0
The name of the file changes week after week because it's something I download from a server. Once I open that file the only Sheet that shows up is under the "report" name.
 
Upvote 0
It sounds like your textboxes are in a userform.

Change the previous 2 lines mentioned to:

VBA Code:
Set ClosedSourceWorkbook = Workbooks.Open TextBox2.Value              ' Get value from TextBox to open the source file

and

VBA Code:
Set ClosedSourceWorkbook = Workbooks.Open TextBox1.Value              ' Get value from TextBox to open the source file
 
Upvote 0
Hi, thanks for your message. No, it's not an User Form, it's an actual Excel Sheet.

When I put the code, it automatically puts it in red. I might be missing something for sure, but don't know what it is
 

Attachments

  • Capture16.PNG
    Capture16.PNG
    22.6 KB · Views: 3
Upvote 0
Based on your Info/Pics, It seems like it is a user form.

So, Let's try the following:

VBA Code:
Private Sub CommandButton1_Click()
'
    Dim ClosedWorkbookSheetName As String
    Dim FileExtention           As String
    Dim NewWorkbookName         As String
    Dim ClosedSourceWorkbook    As Workbook
'
    ClosedWorkbookSheetName = "report"                                                      ' <--- Set this to the sheet name in the closed workbook
    FileExtention = ".xlsx"                                                                 ' <--- Set this to the file extention of workbook to be created
    NewWorkbookName = "ThisWeek"                                                            ' <--- Set this to the name of the workbook to be created
'
    Workbooks.Add.SaveAs Filename:=NewWorkbookName & FileExtention                          ' Create/Name/save New Workbook
'
    ThisWorkbook.Activate                                                                   ' Reactivate this workbook
'
    Application.ScreenUpdating = False                                                      ' Turn ScreenUpdating off
'
    Set ClosedSourceWorkbook = Workbooks.Open(TextBox2.Value)                               ' Get value from TextBox to open the source file
'
'   Copy sheet to beginning of the newly created workbook
    ClosedSourceWorkbook.Sheets(ClosedWorkbookSheetName).Copy Before:=Workbooks(NewWorkbookName & FileExtention).Sheets(1)
    ClosedSourceWorkbook.Close SaveChanges:=False                                           ' Close the source workbook
'
    ThisWorkbook.Activate                                                                   ' Reactivate this workbook
'
    Set ClosedSourceWorkbook = Workbooks.Open(TextBox1.Value)                               ' Get value from TextBox to open the source file
'
'   Copy sheet to beginning of the newly created workbook
    ClosedSourceWorkbook.Sheets(ClosedWorkbookSheetName).Copy Before:=Workbooks(NewWorkbookName & FileExtention).Sheets(1)
    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
End Sub

There are some comments towards the top of the code that have arrows in them, those are the only lines that might need adjusting. I think I have them already set for you though.

Let us know how that works, or not.
 
Upvote 0
Thanks so much! However, I'm still getting an error (Attached), but I have a feeling this code is getting closer.

I appreciate your efforts!
 

Attachments

  • Capture17.PNG
    Capture17.PNG
    18.4 KB · Views: 2
  • Capture18.PNG
    Capture18.PNG
    9.5 KB · Views: 2
Upvote 0
Hi, thanks for your message. No, it's not an User Form, it's an actual Excel Sheet.

Ok, let's see if the text boxes are on a sheet.

Please post a full screen pic of the sheet where your textboxes are.
 
Upvote 0
Sure, kindly find it attached. I just removed the gridlines.

Thanks,

Andres
 

Attachments

  • Capture19.PNG
    Capture19.PNG
    112.5 KB · Views: 2
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top