# Solved: Excel Macro - Invalid procedure call or argument



## computerman29642 (Dec 4, 2007)

I have a macro setup, but each time the code is ran it errors out on this line of code..


```
ChDrive SaveDriveDir
```
This macro was running fine at one time. Nothing has changed.


----------



## Rollin_Again (Sep 4, 2003)

What exactly is this single line of code supposed to do? It's hard to provide an answer without seeing all the code or understanding what this specific line does

Regards,
Rollin


----------



## computerman29642 (Dec 4, 2007)

Here is more of the code


```
SaveDriveDir = CurDir
    MyPath = "C:\Test"
    'ChDrive MyPath
    'ChDir MyPath
    ChDirNet MyPath

ChDrive SaveDriveDir
    ChDir SaveDriveDir
```
I believe the code is suppose to store the current directory.


----------



## slurpee55 (Oct 20, 2004)

2 pages that deal with this almost exactly - you probably have read them, but...
http://www.dailydoseofexcel.com/archives/2005/08/30/changing-the-current-directory/
http://www.rondebruin.nl/copy3.htm#select


----------



## computerman29642 (Dec 4, 2007)

Slurpee, the links may come in handy. Thank you.


----------



## Keebellah (Mar 27, 2008)

Just wondering.
When your macro was running OK where you using Office 2003?
Did it stop working with Office 2007?
There is quiet a lot of code that is no longer supported because of "security" in Office 2007.


----------



## computerman29642 (Dec 4, 2007)

Hans, yes the code was ran on Office 2003. I am running Office 2007, but I have users still using Office 2003.


----------



## Keebellah (Mar 27, 2008)

Okay, but that means it doesn't run with Office 2007. There is a lot of VBA code which is no longer supported under Office 2007. I had similar problems and had to look for alternatives. SO when you write something for Office 2003 you will have to test it under 2007 to see if it's compatible.
This is one example of Microsoft's lack of upward compatability.
I don't know what your code does but you will have to debug it and see what kind of alternatives you have.


----------



## slurpee55 (Oct 20, 2004)

I'm not sure if this will help, but...
http://msdn.microsoft.com/en-us/library/aa711216(VS.71).aspx


----------



## Rollin_Again (Sep 4, 2003)

Can you explain in details what the application is supposed to do? Maybe there is an alternate way of accomplishing what you want.

Regards,
Rollin


----------



## Zack Barresse (Jul 25, 2004)

I'm looking specifically for workbook examples which ran fine in 2003 and not in 2007. If this is one of those, may I have a copy of it?

Back to your issue. Can't you just do what you want to do without changing the directory or drive?


----------



## bomb #21 (Jul 1, 2005)

Zack said:


> I'm looking specifically for workbook examples which ran fine in 2003 and not in 2007 ...


LOL!

I can't be certain because it wasn't me personally, it was a flustered "user". But have a go with ...

... SendKeys.


----------



## Zack Barresse (Jul 25, 2004)

LOL! I do ask for a specific reason, although I can't disclose everything about it as I'm under a non-disclosure agreement. Any workbook examples where used on 2003 worked, then tried directly in 2007 didn't, that's what I'm looking for. SendKeys, well, it's SendKeys. LOL!


----------



## computerman29642 (Dec 4, 2007)

Zack Barresse said:


> I'm looking specifically for workbook examples which ran fine in 2003 and not in 2007. If this is one of those, may I have a copy of it?
> 
> Back to your issue. Can't you just do what you want to do without changing the directory or drive?


Zack, I have sent you a copy of a workbook with the code.


----------



## computerman29642 (Dec 4, 2007)

slurpee55 said:


> I'm not sure if this will help, but...
> http://msdn.microsoft.com/en-us/library/aa711216(VS.71).aspx


Slurpee, thanks for the link. I will have a look.


----------



## computerman29642 (Dec 4, 2007)

Keebellah said:


> Okay, but that means it doesn't run with Office 2007. There is a lot of VBA code which is no longer supported under Office 2007. I had similar problems and had to look for alternatives. SO when you write something for Office 2003 you will have to test it under 2007 to see if it's compatible.
> This is one example of Microsoft's lack of upward compatability.
> I don't know what your code does but you will have to debug it and see what kind of alternatives you have.


Hans, there was only one line that I found so far to keep the code from running in Office 2007. I am still in the process of modifying the code to work in both.

Thanks for all your help.


----------



## computerman29642 (Dec 4, 2007)

Rollin_Again said:


> Can you explain in details what the application is supposed to do? Maybe there is an alternate way of accomplishing what you want.
> 
> Regards,
> Rollin


Rollin, thanks for the response. I am in th eprocessof modifying the code to get it to work in Office 2003 and 2007 (find the alternate way).

When the code runs it will setup the rows and columns. It will prompt the user to select a workbook from a specific folder. Once the folder has been selected by the user, it copies specific data from the selected workbook. The data copied is then pasted in the blank workbook where the code was ran.

The prompt for the user to select a workbook happens three times through teh code.


----------



## slurpee55 (Oct 20, 2004)

Zack Barresse said:


> ... I'm under a non-disclosure agreement.


Oooooh.....


----------



## Rollin_Again (Sep 4, 2003)

Are you trying to change the active directory so that you can prompt the user with an Open Dialogue that allows the user to click on the file to open from that directory?

Regards,
Rollin


----------



## computerman29642 (Dec 4, 2007)

Rollin_Again said:


> Are you trying to change the active directory so that you can prompt the user with an Open Dialogue that allows the user to click on the file to open from that directory?
> 
> Regards,
> Rollin


That would be correct.


----------



## Zack Barresse (Jul 25, 2004)

Wonder if using the file dialog picker would help. You can change your directory in one line of code with that. Haven't had a chance to look at your code yet, so I'm not sure how you're getting the folder. Here is the link..

http://www.eggheadcafe.com/software/aspnet/29568261/vba--open-folder.aspx


----------



## Rollin_Again (Sep 4, 2003)

Trying using an API call along with the *Application.GetOpenFilename* function.

Put this section at the top of your module


```
Private Declare Function SetCurrentDirectory _
Lib "kernel32" _
Alias "SetCurrentDirectoryA" ( _
ByVal lpPathName As String) _
As Long
```
Then call the API


```
Private Sub ChangeDirectory()

Dim vFilename As Variant
Dim vCurrentDirector As String

'Set the variable to current working directory in case you need it later
vCurrentDirectory = CurDir

'Call the API --- Change path to your own desired directory.
SetCurrentDirectory ("C:\Windows")

'Set variable equal to file selected
vFileToOpen = Application.GetOpenFilename

'Open workbook
Application.Workbooks.Open (vFileToOpen)

'Change back to original default directory
ChDir vCurrentDirectory

End Sub
```
Regards,
Rollin


----------



## computerman29642 (Dec 4, 2007)

Zack Barresse said:


> Wonder if using the file dialog picker would help. You can change your directory in one line of code with that. Haven't had a chance to look at your code yet, so I'm not sure how you're getting the folder. Here is the link..
> 
> http://www.eggheadcafe.com/software/aspnet/29568261/vba--open-folder.aspx


Thanks Zack for the response. 

I will take a look at the link above. I used a method close to what Rollin typed in post #22.


----------



## computerman29642 (Dec 4, 2007)

Rollin_Again said:


> Trying using an API call along with the *Application.GetOpenFilename* function.
> 
> Put this section at the top of your module
> 
> ...


Rollin, I ended up with code that is very similar to this code. :up:

Thanks for the response. :up:


----------

