• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dropdown

Istiyak

Istiyak

Member
Is this possible to creat dropdown list based upon files available in Specific folder.


I.e.

folder on desktop = Exported data

Files in folders = 0229.xls, 0429.xls, 02452.xls etc,.


So is this possible to creat dropdown in my actual sheet.


waiting for solution.


Regards

Istiyak
 
Hi, Istiyak!


It's as simple as to set data validation to a list, if you have a list with the filenames.

For building the filename list you can do this:


a) Place this code within a module or related worksheet.

-----

[pre]
Code: 
Option Explicit

Sub X()
' constants
Const ksFolder = "C:UsersAnonymousDesktop"
Const ksExtensions = "*.*"
' declarations
Dim I As Integer, A As String
' start
Range("A:A").ClearContents
I = 0
A = Dir(ksFolder & Application.PathSeparator & ksExtensions)
' process
Do Until A = ""
I = I + 1
ActiveSheet.Cells(I, 1).Value = A
A = Dir()
Loop
' end
End Sub
[/pre]
-----


b) Run it: macro or procedure X (this will retrieve files to column A of related sheet)


c) Set data validation for involved cell to list and point it to column A


Regards!
 
Hi SirJB7..


Thanks for your response


I have modified the code as below still facing issue.


Sub X()

' constants

Const ksFolder = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "Exported Data"

Const ksExtensions = "*.*"

' declarations

Dim I As Integer, A As String

' start

Range("A:A").ClearContents

I = 0

A = Dir(ksFolder & Application.PathSeparator & ksExtensions)

' process

Do Until A = ""

I = I + 1

ActiveSheet.Cells(I, 1).Value = A

A = Dir()

Loop

' end

End Sub


Where i have a folder on my desktop with name "Exported data".

Note : All files are excel files. (For reference only so if u want to remove ksExtensions = "*.*")


Anything plz let me know.


Regards

Istiyak
 
Hi, Istiyak!

Sorry but I didn't understand if you get it working or not. The retrieval for desktop value it's cool, and you always can change wildcards to "*.xl*" to as to get sure that non-Excel files don't come in.

Do you have an issue? What's it?

Regards!
 
Hi SirJB7


Sorry i havn't explain issue properly..


When i m execute that code. VBA editor stops the code at line


Const ksFolder = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "Exported Data"


and there is no value is coming in Column A.


Hope i will be understood.


Regards

Istiyak
 
Hi, Istiyak!


If it's a constant it cannot be build with other than constants, so you must change it to a variable, and assign the value at the beginning of the procedure.


Regards!
 
Hi,


Still Facing Issue. Now i m not getting any but output is not coming.


Sub X()

' constants

Dim desktoploc As String

desktoploc = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "Exported Data"

Const ksFolder = "desktoploc"

Const ksExtensions = "*.*"

' declarations

Dim I As Integer, A As String

' start

Range("A:A").ClearContents

I = 0

A = Dir(ksFolder & Application.PathSeparator & ksExtensions)

' process

Do Until A = ""

I = I + 1

ActiveSheet.Cells(I, 1).Value = A

A = Dir()

Loop

' end

End Sub


Plz rectify
 
Hi, Istiyak!


You deleted the first statement of the procedure:

Option Explicit


It's a good practice to keep it so as to avoid referencing wrong variable/constant names. If you'd have done so, an error would have appeared in the statement:

A = Dir(ksFolder & Application.PathSeparator & ksExtensions)


ksFolder doesn't exist any more.


Regards!
 
HI,


I have cross checked the Folder. it is there.


I also have already added "Option Explicit" just not pasted in comment.


It is not showing anything.


Looking forward for the solution of the same.


Regards

Istiyak
 
Hi, Istiyak!


I run this code and it works.


-----

[pre]
Code: 
Option Explicit

Sub X()
' constants
Dim desktoploc As String
desktoploc = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "Exported Data"
'    Const ksFolder = "desktoploc"
Const ksExtensions = "*.*"
' declarations
Dim I As Integer, A As String
' start
Range("A:A").ClearContents
I = 0
A = Dir(desktoploc & Application.PathSeparator & ksExtensions)
' process
Do Until A = ""
I = I + 1
ActiveSheet.Cells(I, 1).Value = A
A = Dir()
Loop
' end
End Sub
[/pre]
-----


Regards!
 
Heyyyy Cheeerrrrsss


I Got What i want....


Thanks To You SirJB7..


Finallyy you have done it.


always solving my problems..


Thanks again thanks a lot


Regards

Istiyak
 
Hi, Istiyak!


Glad you solved it. But next time, be careful when you modify a provided code. Always press Alt-D L to compile the code and verify everything's still ok after you make modifications. Otherwise you'll be charging people who help you with an overload by working more than once.


Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.


Regards!
 
Hi sirJB.


Need Your Help.


I have some specifications.


Sheet name = UPLD

Name Range = Upld_str_avbl_list

range =UPLD!$AN$2:$AN$65536


Now i want that output in Created name range. so now how to rectify code.


Regards

Istiyak
 
Hi, Istiyak!


How fast did you come back! I haven't time to begin missing you, even a little...

I don't understand your requirement, could you please elaborate a bit more? Thank you.


Regards!
 
hi,


Ya sure..


Your code is given output in Column A.


I want output in Column AN (column no 40) where i have created Name range for smooth functioning.


i have change cell address in your given code.


and then i got the result where i want.


Regards

Istiyak
 
You must log in or register to reply here.
Back
Top