Thank you Basole for your reply. But after the suggested changes when i am
running the code it is showing me error code 400. any specific reason.
On 15 October 2013 17:49, Basole <ricardo...@gmail.com> wrote:
> 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.
>
--
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.
