Solved

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

Posted on 2011-03-13
14
736 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

744 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

12 Experts available now in Live!

Get 1:1 Help Now