Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-03-13
14
Medium Priority
?
751 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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 1800 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 200 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
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 demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

636 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