Excel import sheets with dialog box

[KlausW]

I use this code to import sheets with, it runs optimally, but I wish I could open a dialog box and select the folder where I want to import the sheets from.

Any help will be much appreciated.
Many Thanks

Klaus W

Sub Rektangelafrundedehjørner1_Klik() (It is a button)
Dim ws As Worksheet
   Dim Wbk As Workbook
   Dim Pth As String, Fname As String
      Pth = Range("o1").Value
   Fname = Dir(Pth & "*.xlsm")
   Do While Fname <> ""
      Set Wbk = Workbooks.Open(Pth & Fname)
      For Each ws In Wbk.Worksheets
         If Not ShtExists(ws.Name, ThisWorkbook) Then
            ws.Copy , ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
         End If
      Next ws
      Wbk.Close False
      Fname = Dir
   Loop
End Sub

 

[RoryA]

You could add this function:

Function GetFolder() As String
Dim dlg As FileDialog
Set dlg = Application.FileDialog(msoFileDialogFolderPicker)
dlg.InitialFileName = "C:\"
If dlg.Show = -1 Then
GetFolder = dlg.SelectedItems(1)
End If
End Function

then use:

Pth = GetFolder()

 

[KlausW]

Sorry for the late reply, was just hit by work. I do not quite understand, I put the code in, it went fine but "Pth = GetFolder ()" I can not figure out where to insert.
KW

 

[RoryA]

Instead of this line:

Pth = Range("o1").Value

 

[KlausW]

Hello again, the dialog box appears and I can easily select folders, but I do not include any files above?
KW

 

[RoryA]

You asked to be able to choose the folder. The code already finds any xlsm files in the folder.

 

[KlausW]

Thank you, the code opens the folder, but it does not find or import any xlsm files. I really do not know what is wrong.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[ebea]

When you open the same question on other Sites, remember to make a link to this, according to Forum rules here.

[løst] Overføre Ark / VBA - Eksperten - Computerworld

Nyheder, analyser, viden, blogs og debat om it. Computerworld er dit medie om det digitale Danmark. Computerworld skriver om nye teknologier, hardware, software, informationssamfundet, internettet, it-branchen, telebranchen, it-ledelse, it-strategi, forretnings-it , it-job og meget mere.

www.computerworld.dk

 

[KlausW]

Ok thanks

 

[KlausW]

I don't know if I understandt you ?