Need macro to insert workbook into another workbook using input box

[newnhamm]

I am new to programming in Excel with VBA and I have spent 4 hours searching
the archives for a solution to my challenge.

I am developing a simple application for other users who may or may not have
a lot of computer knowledge. I am making it as user-friendly as possible with
user forms and locking areas of the workbook they shouldn't mess with.

The procedure the users will follow:
1. Open 'my workbook' [which contains all of the macros needed for the rest
of the operation]
2. Autostart an 'input box' which will allow them to select a file [could be
in .xls or .csv format- the file is unique to their location] which will
import the 'worksheet' into 'my workbook' as the last worksheet.
3. Use further userform-based macros to chart their 'worksheet' data.
4. The added 'worksheet' must be deleted automatically when 'my workbook'
closes.

Can anyone suggest a macro [step 2] for choosing, opening and inserting the
unique file and another [step 4] that would delete the worksheet upon closing?


Any help would be appreciated.

Thanks,
Mike

 

[Dave Peterson]

Deleting a worksheet when the workbook closes is a problem. If the user doesn't
save, then the the sheet isn't deleted. To make that automatic seems like more
of problem to me. If the user opens excel and closes and doesn't want to save,
should you force him/her.

How about just deleting the sheet when the code starts?

Something like:

Option Explicit
Sub testme()

Dim wkbk As Workbook
Dim tempWks As Worksheet
Dim myFileName As Variant

myFileName = Application.GetOpenFilename("Excel files, *.xls;*.csv")
If myFileName = False Then
Exit Sub
End If

Set wkbk = Workbooks.Open(Filename:=myFileName)

On Error Resume Next
Application.DisplayAlerts = False
ThisWorkbook.Worksheets("TempSheet").Delete
Application.DisplayAlerts = True
On Error GoTo 0

wkbk.Worksheets(1).Copy _
after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

Set tempWks = ActiveSheet
tempWks.Name = "TempSheet"

'your code here.

End Sub

I am new to programming in Excel with VBA and I have spent 4 hours searching
the archives for a solution to my challenge.

I am developing a simple application for other users who may or may not have
a lot of computer knowledge. I am making it as user-friendly as possible with
user forms and locking areas of the workbook they shouldn't mess with.

The procedure the users will follow:
1. Open 'my workbook' [which contains all of the macros needed for the rest
of the operation]
2. Autostart an 'input box' which will allow them to select a file [could be
in .xls or .csv format- the file is unique to their location] which will
import the 'worksheet' into 'my workbook' as the last worksheet.
3. Use further userform-based macros to chart their 'worksheet' data.
4. The added 'worksheet' must be deleted automatically when 'my workbook'
closes.

Can anyone suggest a macro [step 2] for choosing, opening and inserting the
unique file and another [step 4] that would delete the worksheet upon closing?

Any help would be appreciated.

Thanks,
Mike

 

[newnhamm]

Dave, opening and inserting the sheet works beautifully!

Deleting the sheet when the code starts is an elegant workaround!

Many thanks, you have helped immensely!

Mike

Deleting a worksheet when the workbook closes is a problem. If the user doesn't
save, then the the sheet isn't deleted. To make that automatic seems like more
of problem to me. If the user opens excel and closes and doesn't want to save,
should you force him/her.

How about just deleting the sheet when the code starts?

Something like:

Option Explicit
Sub testme()

Dim wkbk As Workbook
Dim tempWks As Worksheet
Dim myFileName As Variant

myFileName = Application.GetOpenFilename("Excel files, *.xls;*.csv")
If myFileName = False Then
Exit Sub
End If

Set wkbk = Workbooks.Open(Filename:=myFileName)

On Error Resume Next
Application.DisplayAlerts = False
ThisWorkbook.Worksheets("TempSheet").Delete
Application.DisplayAlerts = True
On Error GoTo 0

wkbk.Worksheets(1).Copy _
after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

Set tempWks = ActiveSheet
tempWks.Name = "TempSheet"

'your code here.

End Sub

I am new to programming in Excel with VBA and I have spent 4 hours searching
the archives for a solution to my challenge.

[quoted text clipped - 20 lines]

Thanks,
Mike