Dear Sandeep, i did some testing, and here is working properly, the only
error that appears, is when the macro finds some protected worksheet.
Try using a new worksheet and paste this code below ... and then test to
see if it works.
Option Explicit
#If VBA7 Then
Private Type BROWSEINFO
hOwner As LongPtr
pidlRoot As LongPtr
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As LongPtr
lParam As LongPtr
iImage As Long
End Type
Private Declare PtrSafe Function SHBrowseForFolder Lib "shell32.dll"
Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As LongPtr
Private Declare PtrSafe Function SHGetPathFromIDList Lib "shell32.dll"
Alias "SHGetPathFromIDListA" _
(ByVal pidl As LongPtr, ByVal pszPath As String) As Boolean
#Else
Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type
Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias
"SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long
Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long
#End If
Private Const BIF_RETURNONLYFSDIRS = &H1
Function GetDirectory(Optional msg) As String
On Error Resume Next
Dim bInfo As BROWSEINFO
Dim path As String
Dim r As Long, x As Long, pos As Integer
'Root folder = Desktop
bInfo.pidlRoot = 0&
'Title in the dialog
If IsMissing(msg) Then
bInfo.lpszTitle = "Please select the folder of the excel files to
copy."
Else
bInfo.lpszTitle = msg
End If
'Type of directory to return
bInfo.ulFlags = &H1
'Display the dialog
x = SHBrowseForFolder(bInfo)
'Parse the result
path = Space$(512)
r = SHGetPathFromIDList(ByVal x, ByVal path)
If r Then
pos = InStr(path, Chr$(0))
GetDirectory = Left(path, pos - 1)
Else
GetDirectory = ""
End If
End Function
Sub CombineFiles()
Dim path As String
Dim FileName As String
Dim LastCell As Range
Dim Wkb As Workbook
Dim WS As Worksheet
Dim ThisWB As String
ThisWB = ThisWorkbook.Name
Application.EnableEvents = False
Application.ScreenUpdating = False
path = GetDirectory
FileName = Dir(path & "\*.xls", vbNormal)
Do Until FileName = ""
If FileName <> ThisWB Then
Set Wkb = Workbooks.Open(FileName:=path & "\" & FileName)
For Each WS In Wkb.Worksheets
Set LastCell = WS.Cells.SpecialCells(xlCellTypeLastCell)
If LastCell.Value = "" And LastCell.Address =
Range("$A$1").Address Then
Else
WS.Copy
After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
End If
Next WS
Wkb.Close False
End If
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True
Set Wkb = Nothing
Set LastCell = Nothing
End Sub
Em terça-feira, 15 de outubro de 2013 06h12min23s UTC-3, sandeep chhajer
escreveu:
>
> Dear Excel Gurus,
>
> I have got this Macro from this forum for* adding worksheets from
> different work book kept in a folder,* but now when i am trying this code
> I am getting compiling error message" can not define a public user defined
> type with an object module.
>
> Please help.
>
> Thanks in advance.
>
> Option Explicit
>
> '32-bit API declarations
> Declare Function SHGetPathFromIDList Lib "shell32.dll" _
> Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal _
> pszpath As String) As Long
>
> Declare Function SHBrowseForFolder Lib "shell32.dll" _
> Alias "SHBrowseForFolderA" (lpBrowseInfo As BrowseInfo) _
> As Long
>
> Public Type BrowseInfo
> hOwner As Long
> pIDLRoot As Long
> pszDisplayName As String
> lpszTitle As String
> ulFlags As Long
> lpfn As Long
> lParam As Long
> iImage As Long
> End Type
>
> Function GetDirectory(Optional msg) As String
> On Error Resume Next
> Dim bInfo As BrowseInfo
> Dim path As String
> Dim r As Long, x As Long, pos As Integer
>
> 'Root folder = Desktop
> bInfo.pIDLRoot = 0&
>
> 'Title in the dialog
> If IsMissing(msg) Then
> bInfo.lpszTitle = "Please select the folder of the excel files to
> copy."
> Else
> bInfo.lpszTitle = msg
> End If
>
> 'Type of directory to return
> bInfo.ulFlags = &H1
>
> 'Display the dialog
> x = SHBrowseForFolder(bInfo)
>
> 'Parse the result
> path = Space$(512)
> r = SHGetPathFromIDList(ByVal x, ByVal path)
> If r Then
> pos = InStr(path, Chr$(0))
> GetDirectory = Left(path, pos - 1)
> Else
> GetDirectory = ""
> End If
> End Function
>
> Sub CombineFiles()
> Dim path As String
> Dim FileName As String
> Dim LastCell As Range
> Dim Wkb As Workbook
> Dim WS As Worksheet
> Dim ThisWB As String
>
> ThisWB = ThisWorkbook.Name
> Application.EnableEvents = False
> Application.ScreenUpdating = False
> path = GetDirectory
> FileName = Dir(path & "\*.xls", vbNormal)
> Do Until FileName = ""
> If FileName <> ThisWB Then
> Set Wkb = Workbooks.Open(FileName:=path & "\" & FileName)
> For Each WS In Wkb.Worksheets
> Set LastCell = WS.Cells.SpecialCells(xlCellTypeLastCell)
> If LastCell.Value = "" And LastCell.Address =
> Range("$A$1").Address Then
> Else
> WS.Copy
> After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
> End If
> Next WS
> Wkb.Close False
> End If
> FileName = Dir()
> Loop
> Application.EnableEvents = True
> Application.ScreenUpdating = True
>
> Set Wkb = Nothing
> Set LastCell = Nothing
> End Sub
>
>
> --
> Regards,
> Sandeep Kumar Chhajer.
>
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/groups/opt_out.
