Solved

Excel & VB. Multiple workbooks where file path names are maintained in a seperate workbook.

Posted on 2011-03-13
14
737 Views
Last Modified: 2012-05-11
This is a follow up to a prior question.  Earlier question was in http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_26883177.html 

Essentially I am attempting to keep file paths in a sperate workbook so that when the file path of my projects is changed all that I will have to do is change the paths in one workbook instead of having to find it throughout a lot of VB coding.  I know that if that file path document is moved that that vb coding will have to change, but for my example, it will remain constant on whatever machine I copy these files to.

In this example I have 3 workbooks.  One workbook contains all the Clerk input forms with temp spreadsheets that the data gets copied to my final data workbook.  One workbook contains my data's final saved location. and lastly, One workbook contains my File Paths.


******Problem*******
My vb code Keeps erroring out.
 Stepped into code and the line that it quits on is about 2/3rds through in the '~~> Test if save to workbook is open section

the line is the one after the On Error Goto 0
         Set wb2 = Workbooks.Open(strCopyFromFileInMachineMoneyPull)
Private Sub Worksheet_TESTINGNEWMachinePull()
 ' Retrieve The Last Shift's LVL Totals for Day as of the End of that Shift to copy as values to the ws DataLastShift4MachPull
    '
    Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook
    Dim ws1_1 As Worksheet
    Dim ws2_1 As Worksheet
    Dim ws3_1 As Worksheet
    Dim strCopyFromFileInMachineMoneyPull As String, strSaveToFileInMachineMoneyPull As String
    Dim inputRange_ws1_1 As Range
    Dim outputRange_ws2_1 As Range
    Dim LastRowInput_ws1_1 As Long

'    Application.Visible = False

    Dim intResponse As Integer
    intResponse = MsgBox("Are you sure you want to begin a New Machine Money Pull?", vbOKCancel + vbInformation)
    If intResponse = vbOK Then
      'Resume, the user pressed ok
    
' This defaults the Calendar object to today
        Sheets("Machine Pull").Calendar1.Value = Date
        
        Application.ScreenUpdating = False
        Sheets("Machine Pull").Select
        Sheets("DataLastShift4MachPull").Visible = True

        
        Set wb1 = ActiveWorkbook
        Set ws1_1 = wb1.Sheets("DataLastShift4MachPull")
         
    '~~> Change file path here so I can use a file path workbook to update where files are instead of going to VB code
    Set wb3 = Workbooks.Open("C:\Documents and Settings\Bill.BILL_BACKOFFICE\Desktop\Master Forms\FilePaths.xlsx")
    Set ws3_1 = wb3.Sheets("FilePaths")
    FilePath = ws3_1.Range("E3").Value '<~~ Contains value like C:\Temp\
    wb3.Close savechanges:=False
    
    strCopyFromFileInMachineMoneyPull = FilePath & "Shift Details Data.xlsx"
    strSaveToFileInMachineMoneyPull = "Machine Money Pull.xlsm"
    
    '~~> End Change file path settings
        
        
        '~~> Test if save to workbook is open
        On Error Resume Next
            Set wb2 = Workbooks(Dir(strCopyFromFileInMachineMoneyPull))
            Set ws2_1 = wb2.Sheets("DataLastShift4MachPullTemp")
        
        On Error GoTo 0
        
        If wb2 Is Nothing Then
            Set wb2 = Workbooks.Open(strCopyFromFileInMachineMoneyPull)
            Set ws2_1 = wb2.Sheets("DataLastShift4MachPullTemp")
        
        End If
    
        '~~> Begin Save info
        Set wb2 = ActiveWorkbook
        LastRowInput_ws2_1 = ws2_1.Range("A" & Rows.Count).End(xlUp).Row
        LastRowOutput_ws1_1 = ws1_1.Range("A" & Rows.Count).End(xlUp).Row
        
        Set inputRange_ws2_1 = ws2_1.Range("A5:AC" & LastRowInput_ws2_1)
        Set outputRange_ws1_1 = ws1_1.Range("A" & LastRowOutput_ws1_1)
        
        
        inputRange_ws2_1.Copy
        outputRange_ws1_1.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
        
    '    Application.ScreenUpdating = True
        
        
    ' Save updated data file then close
        ActiveWorkbook.Save
        ActiveWorkbook.Close
    '    Application.Visible = True
        Application.ScreenUpdating = True
    
        Application.ScreenUpdating = False
        
        Sheets("Machine Pull").Select
        Sheets("DataLastShift4MachPull").Visible = False
        
        Range("AA21").Select
        Range("Z23").Select
        Application.Goto Reference:="EmployeeSelector"

        Application.ScreenUpdating = True
        
    Else
      'The user pressed cancel
      'Cancel event
      ActiveWorkbook.Saved = True
      Application.Quit
      
      
    End If
    
End Sub 

Toggle HighlightingOpen in New WindowSelect All

Open in new window

0
Comment
Question by:wlwebb
  • 8
  • 5
14 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35125780
Looking at your script:

Change line 32:


    Set wb3 = Workbooks.Open("C:\Documents and Settings\Bill.BILL_BACKOFFICE\Desktop\Master Forms\FilePaths.xlsx")
to

    Set wb3 = Workbooks.Open("C:\Documents and Settings\Bill.BILL_BACKOFFICE\Desktop\Master Forms\Workpapers\FilePaths.xlsx")

Chris
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35125784
Please ensure that

ws3_1.Range("E3").Value

has the correct path as I mentioned in your other thread :)

Sid
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35125806
I saw the new question in the original thread and moved across to here ... looks as though you missed that post Sid, ;o) (I see you gave the same solution as I put above in that thread!)

Chris
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35125813
Oh. I am sorry Chris. I had refreshed before posting and then when I didn't see anything, simply posted my answer and before the page could completely load, I closed the window so didn't see your post. Sorry again.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35125814
Chris, I just re-read your post. No that is not the answer. That path is correct.

The problem lies in the value of ws3_1.Range("E3").Value as I mentioned in post ID: 35125784 :)

Sid
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35125815
Hey, i'm not complaining, you may be a pain getting so many points :o) but I have no problem with your reasonable attitude.  I was simply having a dig at you for missing that link!

Chris
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35125821
Fair enough :)

BTW if you look at my last post in

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_26883177.html#a35125779

You will see what the exact problem is :)

Sid
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35125837
Not with you.

In line 32 the workbook path is wrong which means the assignment for wb3_1 is also failing ... i.e. it is the abscence of the workpapers sub folder that is the root issue.

Chris
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 450 total points
ID: 35125862
Yes Chris if the file "FilePaths.xlsx" is also in the "Workpapers" folder then what you say is correct However if only the "Shift Details Data.xlsx" is in the "Workpapers" folder and "FilePaths.xlsx" in
"C:\Documents and Settings\Bill.BILL_BACKOFFICE\Desktop\Master Forms\" then OP just needs to ensure that the value in that cell is correct.

wlwebb: Please confirm the exact folder of "FilePaths.xlsx" and "Shift Details Data.xlsx"

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35125869
Seems like what I assumed is correct as per OP's last comments in the other thread.

Sid
0
 
LVL 59

Assisted Solution

by:Chris Bottomley
Chris Bottomley earned 50 total points
ID: 35125908
>>> Seems like what I assumed is correct as per OP's last comments in the other thread.

Getting less impressed!, wlwebb, specifically stated the path for the file as including the workpapers folder and the code specifically excluded it.  SO based on that I posted the fisrt comment which is exactly what you suggested in your last (accepted) post in the old thread so I really cannot understand what it is you are saying that decries from my first post.  That said however it does not seem to me that there is something I need to pick up from your posts that changes my understanding so i'm outta here!

Chris
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35125938
Chris :)

Ok let me explain

The scenario is that the file "FilePaths.xlsx" is in this folder

"C:\Documents and Settings\Bill.BILL_BACKOFFICE\Desktop\Master Forms\"

and the file "Shift Details Data.xlsx" is in this folder

"C:\Documents and Settings\Bill.BILL_BACKOFFICE\Desktop\Master Forms\Workpapers\"

The path of "Shift Details Data.xlsx" was mentioned incorrectly in ws3_1.Range("E3").Value. The path that was mentioned was

"C:\Documents and Settings\Bill.BILL_BACKOFFICE\Desktop\Master Forms\"

and hence the code was not able to find the file "Shift Details Data.xlsx"

Sorry, if there is any confusion from my end :)

Sid
0
 

Author Closing Comment

by:wlwebb
ID: 35125955
Sid's answers are fantastic.  However, in this case I did award Chris 50 points for sticking with the problem.  Thanks to both.  But for those that may read this thread somewhere in the future, please refer to Sid's final solution.  Ultimately it was a problem with the path that I had saved the file to and hadn't caught the difference in paths.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35126024
wlwebb: Thank you for the grades and the clarification.

Sid
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now