Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-13-2018, 07:55 AM   #1
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 601
Thanks: 12
Thanked 9 Times in 8 Posts
smiler44 is on a distinguished road
why wont the use of a variable work

I found some code on the internet and have chopped and changed it around but for the life of me I cannot understand why the use of a variable will not work. The code randomly selects an mp3 file and goes on to play it.
The variable is to get the running time of the mp3 file.

if I don't use a variable it works fine but I need to use a variable


Code:
Dim fd 'file duration of song
Dim filetoplay ' the mp3 file to play
Global spt As String

Sub start()
' play music for pre determined time
Do Until Sheet1.TextBox2.Value > Sheet1.TextBox1.Value
Call randomlyselectsong
Loop
If Sheet1.CheckBox1.Value = True Then ' shutdown laptop
Call shutdown 'shut down laptop
End If
End Sub

Code:
Dim fs, f1, fc, s
 Dim ftp

Sub randomlyselectsong()
Dim counter ' used to just lay 10 randon songs used with do until and loop
Dim a ' amount of time to pause for while song plays
'other wise next song play immediately
counter = 0
Do Until counter = 10 ' just allows 10 files to be randomly selected
' could remove the Do until loop
Set fs = CreateObject("scripting.filesystemobject")
Set f = fs.GetFolder("C:\users\Dirk pitt\Documents\Dads Music")
Randomize
i = CInt((Rnd() * f.Files.Count) + 1)
j = 1
For Each fi In f.Files
If j = i Then
ftp = fi.Name 'fi, randomly selected song
Call RecursiveDir(Directory)

filetoplay = """C:\path" & ftp
Shell "C:\path\wmplayer /play /close " & filetoplay
counter = counter + 1
pause (a)
End If
j = j + 1
Next
Loop
End Sub
'this is where is fails. if I unrem dim ftp, ' Range("a1").Value = FileInfo("C:\path", ftp, 27)
and rem Range("a1").Value = FileInfo("C:\path\mp3", "4 Non Blondes - What's Up.mp3", 27)

I get the word length returned as the value, why?

if I then unrem 'ftp = "4 Non Blondes - What's Up.mp3"

it works fine


Code:
Public Sub RecursiveDir(ByVal currdir As String)
'Dim ftp
'ftp = "4 Non Blondes - What's Up.mp3"
   
  Range("A1").Select
  Range("a1").Value = FileInfo("C:\path\mp3", "4 Non Blondes - What's Up.mp3", 27)
 ' Range("a1").Value = FileInfo("C:\path", ftp, 27)

             
End Sub
Function FileInfo(path, filename, item) As Variant
' this gets the file duration
    Dim objShell As IShellDispatch4
    Dim objFolder As Folder3
    Dim objFolderItem As FolderItem2
    
  
    Set objShell = CreateObject("Shell.Application")
    Set objFolder = objShell.Namespace(path)
    Set objFolderItem = objFolder.ParseName(filename)
    FileInfo = objFolder.GetDetailsOf(objFolderItem, item)
       
    Set objShell = Nothing
    Set objFolder = Nothing
    Set objFolderItem = Nothing
End Function

please can you explain why using the variable ftp wont work?
spent hours trying to work out why.

thank you

smiler44


Last edited by Uncle Gizmo; 10-13-2018 at 09:46 AM. Reason: sorted code tags
smiler44 is offline   Reply With Quote
Old 10-13-2018, 08:14 AM   #2
MajP
Newly Registered User
 
Join Date: May 2018
Posts: 479
Thanks: 6
Thanked 115 Times in 113 Posts
MajP will become famous soon enough
Re: why wont the use of a variable work

This an absolute mess. Declare variables properly. Outside of modules variables are declared Private or Public not dim or global. Inside procedures declare them for what they are.
Dim fs as object
Dim F1 as object
dim i as integer
dim j as integer
dim FTP as string

Then put option explicit at the top of your module. After fixing that properly indent your code and use the code # tag so that this is readable.
MajP is offline   Reply With Quote
Old 10-13-2018, 08:21 AM   #3
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 601
Thanks: 12
Thanked 9 Times in 8 Posts
smiler44 is on a distinguished road
Re: why wont the use of a variable work

majp, thank you for the quick reply.
Sorry, the code is from different modules hence some of the declarations appear in the middle of nowhere.

All but one of the dims's you refer to are from code taken from the net and I have left them where they were. I'm a total ammeter.

Even though it is a mess are you able to make heads or tails of it and guide me?

smiler44

smiler44 is offline   Reply With Quote
Old 10-13-2018, 08:34 AM   #4
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,398
Thanks: 291
Thanked 374 Times in 359 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: why wont the use of a variable work

I think you have grabbed VBScript code, which is similar to VBA.?
I also think the path needs a trailing backslash ?
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Last edited by Gasman; 10-13-2018 at 08:43 AM.
Gasman is offline   Reply With Quote
Old 10-13-2018, 08:37 AM   #5
MajP
Newly Registered User
 
Join Date: May 2018
Posts: 479
Thanks: 6
Thanked 115 Times in 113 Posts
MajP will become famous soon enough
Re: why wont the use of a variable work

It is very hard to read. Can you go back and use the # tag at the top of the edit window. Select the code and then hit #. It all runs together. This will do something like

My code example
Code:
Public Sub InsertRoute(CurrentRoute As Route)
  Dim DB As DAO.Database
  Dim rs As DAO.Recordset
  Dim CurrentCity As Integer
  Dim PreviousCityName As String
  Dim CurrentCityName As String
  Dim strSql As String
  
  Set DB = CurrentDb
  Set rs = DB.OpenRecordset("tbllocationsdistances", dbOpenDynaset)
  PreviousCityName = CurrentRoute.Item(1).CityName
  For CurrentCity = 2 To CurrentRoute.Cities.count
    CurrentCityName = CurrentRoute.Item(CurrentCity).CityName
    strSql = "StartCity = '" & PreviousCityName & "' AND EndCity = '" & CurrentCityName & "'"
    rs.FindFirst strSql
    'Debug.Print RS!PathID
    InsertNewLeg rs!PathID, CurrentCity - 1
    PreviousCityName = CurrentCityName
  Next CurrentCity
End Sub
MajP is offline   Reply With Quote
Old 10-13-2018, 09:49 AM   #6
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 601
Thanks: 12
Thanked 9 Times in 8 Posts
smiler44 is on a distinguished road
Re: why wont the use of a variable work

It is very hard to read. Can you go back and use the # tag at the top of the edit window. Select the code and then hit #. It all runs together.

sorry Majp, I don't understand

smiler44
smiler44 is offline   Reply With Quote
Old 10-13-2018, 09:52 AM   #7
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 9,458
Thanks: 358
Thanked 773 Times in 738 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: why wont the use of a variable work

It's never a good idea to use a Real Words in your MS Access database. Take for instance your function name "Start", this should be changed to something like:- fStart... If you change it to that then you can be pretty sure that you are not going to cause any conflicts and problems in your code. The same goes for table names; field names; any names really! Avoid Real Words --- in other words, use a naming convention, your own convention, anything to just destroy any chance of a word being passed through and affecting your code somehow by your code thinking that it's some sort of VBA command...

__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is online now   Reply With Quote
The Following User Says Thank You to Uncle Gizmo For This Useful Post:
smiler44 (10-13-2018)
Old 10-13-2018, 10:19 AM   #8
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 601
Thanks: 12
Thanked 9 Times in 8 Posts
smiler44 is on a distinguished road
Re: why wont the use of a variable work

sorry for wasting your time, I found it at least I think I have. I will if I may put it down to dyslexia, otherwise, it is down to my stupidity.

I have been running the code and running the code and finally I think the penny has dropped.


In Public Sub RecursiveDir(ByVal currdir As String) and Public Sub randomlyselectsong() the directories are different.

Made them the same and although I am so very confused not helped by a power outage I think this was the problem.


once again my sincere apologies and thank you for looking

smiler44
smiler44 is offline   Reply With Quote
Old 10-13-2018, 10:29 AM   #9
MajP
Newly Registered User
 
Join Date: May 2018
Posts: 479
Thanks: 6
Thanked 115 Times in 113 Posts
MajP will become famous soon enough
Re: why wont the use of a variable work

Need to learn how to pass variables to procedures. There is a lot on the web. That is the way you should be doing it not by global variables.

I would execpt to see something more like


Code:
Public Sub RecursiveDir(ByVal ftp As String)
  Range("A1").Select
 ' Range("a1").Value = FileInfo("C:\path", ftp, 27)
end sub
Then somewhere else in another procedure a call

call recursiveDir(FTP)

You cannot have a module level (global) variable and a local (procedure) level variable with the same name. If the global variable FTP has a value "C:\someMP3"
and you then dimension a local variable also called FTP it will look for the local one.

So if you do this
Code:
Public Sub RecursiveDir(ByVal currdir As String)
Dim ftp
You create a local variable FTP with value of ""
MajP is offline   Reply With Quote
Old 10-14-2018, 12:59 AM   #10
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 601
Thanks: 12
Thanked 9 Times in 8 Posts
smiler44 is on a distinguished road
Re: why wont the use of a variable work

thanks Majp, once I get the code to work I will try to tidy it up.

I am almost there, just one problem that I thought was easy has me beaten. I'll try a bit longer to solve it, if I am still beaten I will create a new thread, please keep your eyes peeled

thanks again

smiler44

smiler44 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Dont know why this wont work? Sam Summers Modules & VBA 27 05-20-2016 08:52 AM
VBA for query wont work shapman Queries 5 10-03-2007 07:55 AM
Why wont this work JTQ911 Queries 1 07-30-2007 06:51 AM
Please help, simple radio button code wont work and I cant work out why andyj2k1 Forms 6 02-26-2007 04:33 AM
why wont my code work? Treason Modules & VBA 1 03-04-2003 10:22 PM




All times are GMT -8. The time now is 04:53 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World