Problem With ChDir :: MrExcel Message Board


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

Problem With ChDir
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

AllShookDown
Board Regular


Joined: 07 May 2002
Posts: 48
Location: Wisconsin
Flag: Usa

Status: Offline

 Reply with quote  

Problem With ChDir

I'm working on a macro that's going to be in a file that will be copied out to several LAN's within our company.

The macro has to open one or more workbooks and copy some data into the main workbook. Because there are going to be several versions of the main workbook I'd rather not hard code the path to the other workbooks.

If the macro can't locate the sub-workbooks I've got a message box to tell the user to put them all in the same directory.

I had it all set up and running in a test directory. I copied the test files to a directory on a different drive, opened the main workbook through windows explorer and I got the error message I set up. Stepping through it I could see that it truly couldn't find the workbooks to open. It seemed to work ok if I opened it using File>Open though. Or sometimes if I saved the file and re-opened it.

I moved the chDir ThisWorkbook.Path statement to the very beginning of the code and everything seemed to be working again. Then I copied the files out to a different LAN and tested it. The macro runs and opens the files and copies the data but...when I step through it and check the properties on the files it's opening, I can see that it's opening the files that were on the drive I was previously working in on my own LAN. The file names in the code are definitely correct.

I can't figure out what's going wrong here. Why isn't the chDir thing working?


code:
Sub CopyPremiumData()

'Copies the premium data from the State Premium Planning Model into
'Branch copy of the Branch Planning Model.
'
ChDir ThisWorkbook.Path

RegionBook = ActiveWorkbook.Name
Dim StateBook(1 To 2) As Variant

StateBook(1) = "Freeport Premium Planning Model.xls"
StateBook(2) = "Springfield Premium Planning Model.xls"
'StateBook(3) = "Iowa Premium Planning Model.xls"
'StateBook(3) = "Kansas Premium Planning Model.xls"

Dim x As Integer
   
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    Workbooks(RegionBook).Activate
   
    Sheets("PremData").Visible = True
    Application.Goto Reference:="PremiumDelete"
    Selection.ClearContents
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
For x = 1 To 2

    Workbooks(RegionBook).Activate
    Application.Goto Reference:="PremInStart"
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
   
'    ChDir ThisWorkbook.Path
   
On Error GoTo ErrorCheck
    Workbooks.Open StateBook(x)
On Error GoTo 0

    Workbooks(StateBook(x)).Activate
    Calculate
   
    Sheets("BPMData").Visible = True
    Application.Goto Reference:="DataOut"
    Selection.Copy
    Workbooks(RegionBook).Activate
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Workbooks(StateBook(x)).Activate
    ActiveWorkbook.Close

Next x

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    Workbooks(RegionBook).Activate
    Sheets("PremData").Visible = False
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
   
Exit Sub

ErrorCheck:
MsgBox "All workbooks must be in the same directory."

End Sub


Post Fri Sep 19, 2003 4:06 pm 
 View user's profile Send private message

Juan Pablo González
MrExcel MVP


Joined: 09 Feb 2002
Posts: 8580
Location: Marion, IL
Flag: Colombia

Status: Offline

 Reply with quote  

Re: Problem With ChDir

*Maybe* you need to change drives too first ?

ChDrive Left$(ThisWorkbook.Path, 1)
ChDir ThisWorkbook.Path
'Rest of the code...
_________________
Regards,

Juan Pablo González
MrExcel.com Consulting

Read the Articles List and check out our Recommended links and Add-Ins

Post Fri Sep 19, 2003 4:09 pm 
 View user's profile Send private message Visit poster's website

AllShookDown
Board Regular


Joined: 07 May 2002
Posts: 48
Location: Wisconsin
Flag: Usa

Status: Offline

 Reply with quote  

Re: Problem With ChDir

Thank you so much. That did the trick. I had to have someone in the branch office test it for me because my definition of their drive is seven characters instead of G:, but it worked!

Post Tue Sep 23, 2003 11:39 am 
 View user's profile Send private message
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.