Hi Sandeep, try this:
> Replaces the highlighted code for this:
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
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
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.
