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.