I found this program to import many worksheets from different location.
1. How can I change it to stop after one choice ?
2. How can I change it to stop after two choice ?


Sub GetSheets()

Dim s As String

Dim fd As FileDialog
Dim ffs As FileDialogFilters
Dim wb As Workbook
Dim xb As Workbook
Set xb = ActiveWorkbook

'Get user to pick file
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
Set ffs = .Filters
With ffs
..Clear
..Add "Excel Files", "*.xls"
End With
..AllowMultiSelect = False
If .Show Then s = .SelectedItems(1)
End With

'Turn off prompts
Application.DisplayAlerts = False

'Open chosen workbook
Set wb = Workbooks.Open(s, False)

Dim ws As Worksheet
For Each ws In wb.Worksheets
ws.Copy xb.Worksheets(1)
Next ws


'Close workbook
wb.Close

'Switch prompts back on
Application.DisplayAlerts = True

End Sub

If you mean import just one sheet...

Replace the below 3 line code

'For Each ws In wb.Worksheets
'ws.Copy xb.Worksheets(1)
'Next ws

'to import the 1st sheet
ws.Copy xb.Worksheets(1)

'to import the second sheet
ws.Copy xb.Worksheets(2)

'to import any sheet
ws.Copy xb.Worksheets("Sheetname")

If this post helps click Yes
---------------
Jacob Skaria


"Sverre" wrote:

I found this program to import many worksheets from different location.
1. How can I change it to stop after one choice ?
2. How can I change it to stop after two choice ?


Sub GetSheets()

Dim s As String

Dim fd As FileDialog
Dim ffs As FileDialogFilters
Dim wb As Workbook
Dim xb As Workbook
Set xb = ActiveWorkbook

'Get user to pick file
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
Set ffs = .Filters
With ffs
.Clear
.Add "Excel Files", "*.xls"
End With
.AllowMultiSelect = False
If .Show Then s = .SelectedItems(1)
End With

'Turn off prompts
Application.DisplayAlerts = False

'Open chosen workbook
Set wb = Workbooks.Open(s, False)

Dim ws As Worksheet
For Each ws In wb.Worksheets
ws.Copy xb.Worksheets(1)
Next ws


'Close workbook
wb.Close

'Switch prompts back on
Application.DisplayAlerts = True

End Sub

Oops. Please ignore the previous post

Replace the below 4 line code
'Dim ws As Worksheet
'For Each ws In wb.Worksheets
'ws.Copy xb.Worksheets(1)
'Next ws

'to import the 1st sheet
wb.Worksheets(1).Copy xb.Worksheets(1)

'to import the 2nd sheet
wb.Worksheets(2).Copy xb.Worksheets(1)

'to import the any sheet
wb.Worksheets("sheetname").Copy xb.Worksheets(1)

--
If this post helps click Yes
---------------
Jacob Skaria


"Sverre" wrote:

I found this program to import many worksheets from different location.
1. How can I change it to stop after one choice ?
2. How can I change it to stop after two choice ?


Sub GetSheets()

Dim s As String

Dim fd As FileDialog
Dim ffs As FileDialogFilters
Dim wb As Workbook
Dim xb As Workbook
Set xb = ActiveWorkbook

'Get user to pick file
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
Set ffs = .Filters
With ffs
.Clear
.Add "Excel Files", "*.xls"
End With
.AllowMultiSelect = False
If .Show Then s = .SelectedItems(1)
End With

'Turn off prompts
Application.DisplayAlerts = False

'Open chosen workbook
Set wb = Workbooks.Open(s, False)

Dim ws As Worksheet
For Each ws In wb.Worksheets
ws.Copy xb.Worksheets(1)
Next ws


'Close workbook
wb.Close

'Switch prompts back on
Application.DisplayAlerts = True

End Sub

Thank you very much, it works perfect. Answer in one minute, FANTASTIC.

I have an additional question:
Is it possible to give the dialogbox 1 and 2 different name ?
I want the names:

Dialogbox 1: Importer elevdata
Dialogbox 2: Importer lærerdata

Sverre,

..AllowMultiSelect = False
..Title = "Importer elevdata"

Just after Multiselect option add the title. Multiselect set to True will
allow multiple files to be selected....

If this post helps click Yes
---------------
Jacob Skaria


"Sverre" wrote:

Thank you very much, it works perfect. Answer in one minute, FANTASTIC.

I have an additional question:
Is it possible to give the dialogbox 1 and 2 different name ?
I want the names:

Dialogbox 1: Importer elevdata
Dialogbox 2: Importer lærerdata

Thank you very much. It works for 1 sheet import. For 2 sheets I got trubble,
but never maind. I can introduce 2 buttons one for each. I am close to end my
task with good help from you. I gess it is to late for me to be an VB
programmer. I startet programming in Fortran for two yers in 1969. After that
I got a new job an have'nt done any programming since then.
Thank you for all help.
I will be back next week with some run time errors I do no understand.
Have a nice weekend