• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Issue reading in files across network from SharePoint

PaulF

PaulF

Active Member
Hello ALL!

I'm working a project that uses a release form to authorize work activity.

Release_Template_Blank1.jpg


There is lots of Conditional Formatting to assist them with getting all of the required data in with proper formatting.

When populated:

Release_Template_Populated1.jpg


There is a fair amount of VBA in the form as the math for the hours gets complicated based on a bunch of various rules based on days, labor catagories, and different Skill Crafts.

I read the file names in from a directory. They can then check the ones they need to read in and or update:

Import.jpg


This populates a manning report:

Manning_Report.jpg


That rolls up to:

Manning_Rollup1.jpg


Some background: I use Excel 2016, the client uses Excel 2007 in an enterprise environment.

I can read in 1-150 release locally from my NAS on my home network. Most times they can read in the release, but starting yesterday they are not able to read the release in and the debugger lands on the same line every time:

Workbooks(OWB).Close SaveChanges:=False
 
Here is my code to read in release files once the checkboxes are checked: (Please be mindful that I consider myself a newer VBA coder.)

Code: 
Sub OpenFiles()
Dim folderpath As String
Dim cell As Range
Dim r, lrow As Single
Dim CWB As Workbook
Dim OWB As String
Dim OWBS1 As String
Dim OWBS2 As String
Dim CWBS1 As String
Dim GCell As Range
Dim A As Worksheet
Dim B As Worksheet
Dim chkbx As Variant
Dim RSLrow As Single


OWBS1 = "Release"
CWBS1 = "Import"

folderpath = Range("B1").Value
Set CWB = ActiveWorkbook

If Right(folderpath, 1) <> "\" Then
  folderpath = folderpath & "\"
End If

Application.Calculation = xlManual

For Each chkbx In ActiveSheet.CheckBoxes
    If chkbx.Value = 1 Then
        For r = 1 To Rows.Count
            If Cells(r, 1).Top = chkbx.Top Then
                    Workbooks.Open Filename:=folderpath & Range("A" & r).Value
                    CWB.Activate
                    Set A = CWB.Worksheets(CWBS1)
                    OWB = Range("A" & r).Value
                    Set B = Workbooks(OWB).Worksheets(1)
                       
                        If B.Range("A1").Value <> "GP Strategies Corp Rev1" Then
                       
                            A.Range("D" & r).Value = "NOT A RELEASE FORM"
                           
                        ElseIf B.Range("AB16").Value > 0 Then
                       
                            A.Range("D" & r).Value = B.Range("AD16").Value
                           
                            Else
                       
                              ' Name of Vendor
                              A.Range("D" & r).Value = B.Range("E4").Value
                              ' Date Submitted
                              A.Range("E" & r).Value = B.Range("Q4").Value
                              'Contract No
                              A.Range("F" & r).Value = B.Range("E5").Value
                              ' Contract Job No
                              A.Range("G" & r).Value = B.Range("K5").Value
                              ' Unit
                              A.Range("H" & r).Value = B.Range("F56").Value
                              ' WBS
                              A.Range("I" & r).Value = B.Range("M20").Value
                              ' NTX
                              A.Range("J" & r).Value = B.Range("P47").Value
                              ' Work Description
                              A.Range("K" & r).Value = B.Range("E16").Value
                              RSLrow = CWB.Worksheets("Release Summary").Range("B" & Rows.Count).End(xlUp).Row
                              CWB.Worksheets("Release Summary").Range("B" & RSLrow).Value = B.Range("E16").Value
                              ' Supervisor
                              A.Range("L" & r).Value = B.Range("J10").Value
                              ' Resource Count
                              A.Range("M" & r).Value = B.Range("L62").Value
                              ' Resource Hours
                              ' A.Range("N" & r).Value = B.Range("Q36").Value
                              A.Range("N" & r).Formula = Excel.WorksheetFunction.Sum(B.Range("Q26:Q35"))
                              ' Start Date
                              A.Range("O" & r).Value = B.Range("F20").Value
                              ' End Date
                              A.Range("P" & r).Value = B.Range("F21").Value
                              ' Weeks
                              A.Range("Q" & r).Value = B.Range("I20").Value
                             
                              ' Skill Craft 1
                              A.Range("R" & r).Value = B.Range("D26").Value
                              ' Skill Craft 1 Start Date
                              A.Range("S" & r).Value = B.Range("G26").Value
                              ' Skill Craft 1 Rate End Date
                              A.Range("T" & r).Value = B.Range("I26").Value
                              ' Skill Craft 1 Quanity
                              A.Range("U" & r).Value = B.Range("K26").Value
                              ' Skill Craft 1 Days
                              A.Range("V" & r).Value = B.Range("L26").Value
                              ' Skill Craft 1 Hours
                              A.Range("W" & r).Value = B.Range("M26").Value
                              ' Skill Craft 1 Straight Rate
                              A.Range("X" & r).Value = B.Range("Z26").Value
                              ' Skill Craft 1 Over Time Rate
                              A.Range("Y" & r).Value = B.Range("AA26").Value
                            ' Skill Craft 1 Hours
                              A.Range("Z" & r).Value = B.Range("Q26").Value
                              ' Skill Craft 1 Total
                              A.Range("AA" & r).Value = B.Range("S26").Value
                             
                              ' Skill Craft 2
                              A.Range("AB" & r).Value = B.Range("D27").Value
                              ' Skill Craft 2 Start Date
                              A.Range("AC" & r).Value = B.Range("G27").Value
                              ' Skill Craft 2 Rate End Date
                              A.Range("AD" & r).Value = B.Range("I27").Value
                              ' Skill Craft 2 Quanity
                              A.Range("AE" & r).Value = B.Range("K27").Value
                              ' Skill Craft 2 Days
                              A.Range("AF" & r).Value = B.Range("L27").Value
                              ' Skill Craft 2 Hours
                              A.Range("AG" & r).Value = B.Range("M27").Value
                              ' Skill Craft 2 Straight Rate
                              A.Range("AH" & r).Value = B.Range("Z27").Value
                              ' Skill Craft 2 Over Time Rate
                              A.Range("AI" & r).Value = B.Range("AA27").Value
                              ' Skill Craft 2 Hours
                              A.Range("AJ" & r).Value = B.Range("Q27").Value
                              ' Skill Craft 2 Total
                              A.Range("AK" & r).Value = B.Range("S27").Value
                             
                              ' Skill Craft 3
                              A.Range("AL" & r).Value = B.Range("D28").Value
                              ' Skill Craft 3 Start Date
                              A.Range("AM" & r).Value = B.Range("G28").Value
                              ' Skill Craft 3 Rate End Date
                              A.Range("AN" & r).Value = B.Range("I28").Value
                              ' Skill Craft 3 Quanity
                              A.Range("AO" & r).Value = B.Range("K28").Value
                              ' Skill Craft 3 Days
                              A.Range("AP" & r).Value = B.Range("L28").Value
                              ' Skill Craft 3 Hours
                              A.Range("AQ" & r).Value = B.Range("M28").Value
                              ' Skill Craft 3 Straight Rate
                              A.Range("AR" & r).Value = B.Range("Z28").Value
                              ' Skill Craft 3 Over Time Rate
                              A.Range("AS" & r).Value = B.Range("AA28").Value
                              ' Skill Craft 3 Hours
                              A.Range("AT" & r).Value = B.Range("Q28").Value
                              ' Skill Craft 3 Total
                              A.Range("AU" & r).Value = B.Range("S28").Value
                                                                           
                              ' Skill Craft 4
                              A.Range("AV" & r).Value = B.Range("D29").Value
                              ' Skill Craft 4 Start Date
                              A.Range("AW" & r).Value = B.Range("G29").Value
                              ' Skill Craft 4 Rate End Date
                              A.Range("AX" & r).Value = B.Range("I29").Value
                              ' Skill Craft 4 Quanity
                              A.Range("AY" & r).Value = B.Range("K29").Value
                              ' Skill Craft 4 Days
                              A.Range("AZ" & r).Value = B.Range("L29").Value
                              ' Skill Craft 4 Hours
                              A.Range("BA" & r).Value = B.Range("M29").Value
                              ' Skill Craft 4 Straight Rate
                              A.Range("BB" & r).Value = B.Range("Z29").Value
                              ' Skill Craft 4 Over Time Rate
                              A.Range("BC" & r).Value = B.Range("AA29").Value
                              ' Skill Craft 4 Hours
                              A.Range("BD" & r).Value = B.Range("Q29").Value
                              ' Skill Craft 4 Total
                              A.Range("BE" & r).Value = B.Range("S29").Value
 
Code: 
                              ' Skill Craft 5
                              A.Range("BF" & r).Value = B.Range("D30").Value
                              ' Skill Craft 5 Start Date
                              A.Range("BG" & r).Value = B.Range("G30").Value
                              ' Skill Craft 5 Rate End Date
                              A.Range("BH" & r).Value = B.Range("I30").Value
                              ' Skill Craft 5 Quanity
                              A.Range("BI" & r).Value = B.Range("K30").Value
                              ' Skill Craft 5 Days
                              A.Range("BJ" & r).Value = B.Range("L30").Value
                              ' Skill Craft 5 Hours
                              A.Range("BK" & r).Value = B.Range("M30").Value
                              ' Skill Craft 5 Straight Rate
                              A.Range("BL" & r).Value = B.Range("Z30").Value
                              ' Skill Craft 5 Over Time Rate
                              A.Range("BM" & r).Value = B.Range("AA30").Value
                              ' Skill Craft 5 Hours
                              A.Range("BN" & r).Value = B.Range("Q30").Value
                              ' Skill Craft 5 Total
                              A.Range("BO" & r).Value = B.Range("S30").Value
                             
                              ' Skill Craft 6
                              A.Range("BP" & r).Value = B.Range("D31").Value
                              ' Skill Craft 6 Start Date
                              A.Range("BQ" & r).Value = B.Range("G31").Value
                              ' Skill Craft 6 Rate End Date
                              A.Range("BR" & r).Value = B.Range("I31").Value
                              ' Skill Craft 6 Quanity
                              A.Range("BS" & r).Value = B.Range("K31").Value
                              ' Skill Craft 6 Days
                              A.Range("BT" & r).Value = B.Range("L31").Value
                              ' Skill Craft 6 Hours
                              A.Range("BU" & r).Value = B.Range("M31").Value
                              ' Skill Craft 6 Straight Rate
                              A.Range("BV" & r).Value = B.Range("Z31").Value
                              ' Skill Craft 6 Over Time Rate
                              A.Range("BW" & r).Value = B.Range("AA31").Value
                              ' Skill Craft 6 Hours
                              A.Range("BX" & r).Value = B.Range("Q31").Value
                              ' Skill Craft 6 Total
                              A.Range("BY" & r).Value = B.Range("S31").Value
                             
                              ' Skill Craft 7
                              A.Range("BZ" & r).Value = B.Range("D32").Value
                              ' Skill Craft 7 Start Date
                              A.Range("CA" & r).Value = B.Range("G32").Value
                              ' Skill Craft 7 Rate End Date
                              A.Range("CB" & r).Value = B.Range("I32").Value
                              ' Skill Craft 7 Quanity
                              A.Range("CC" & r).Value = B.Range("K32").Value
                              ' Skill Craft 7 Days
                              A.Range("CD" & r).Value = B.Range("L32").Value
                              ' Skill Craft 7 Hours
                              A.Range("CE" & r).Value = B.Range("M32").Value
                              ' Skill Craft 7 Straight Rate
                              A.Range("CF" & r).Value = B.Range("Z32").Value
                              ' Skill Craft 7 Over Time Rate
                              A.Range("CG" & r).Value = B.Range("AA32").Value
                              ' Skill Craft 7 Hours
                              A.Range("CH" & r).Value = B.Range("Q32").Value
                              ' Skill Craft 7 Total
                              A.Range("CI" & r).Value = B.Range("S32").Value
                             
                              ' Skill Craft 8
                              A.Range("CJ" & r).Value = B.Range("D33").Value
                              ' Skill Craft 8 Start Date
                              A.Range("CK" & r).Value = B.Range("G33").Value
                              ' Skill Craft 8 Rate End Date
                              A.Range("CL" & r).Value = B.Range("I33").Value
                              ' Skill Craft 8 Quanity
                              A.Range("CM" & r).Value = B.Range("K33").Value
                              ' Skill Craft 8 Days
                              A.Range("CN" & r).Value = B.Range("L33").Value
                              ' Skill Craft 8 Hours
                              A.Range("CO" & r).Value = B.Range("M33").Value
                              ' Skill Craft 8 Straight Rate
                              A.Range("CP" & r).Value = B.Range("Z33").Value
                              ' Skill Craft 8 Over Time Rate
                              A.Range("CQ" & r).Value = B.Range("AA33").Value
                              ' Skill Craft 8 Hours
                              A.Range("CR" & r).Value = B.Range("Q33").Value
                              ' Skill Craft 8 Total
                              A.Range("CS" & r).Value = B.Range("S33").Value
                             
                              ' Skill Craft 9
                              A.Range("CT" & r).Value = B.Range("D34").Value
                              ' Skill Craft 9 Start Date
                              A.Range("CU" & r).Value = B.Range("G34").Value
                              ' Skill Craft 9 Rate End Date
                              A.Range("CV" & r).Value = B.Range("I34").Value
                              ' Skill Craft 9 Quanity
                              A.Range("CW" & r).Value = B.Range("K34").Value
                              ' Skill Craft 9 Days
                              A.Range("CX" & r).Value = B.Range("L34").Value
                              ' Skill Craft 9 Hours
                              A.Range("CY" & r).Value = B.Range("M34").Value
                              ' Skill Craft 9 Straight Rate
                              A.Range("CZ" & r).Value = B.Range("Z34").Value
                              ' Skill Craft 9 Over Time Rate
                              A.Range("DA" & r).Value = B.Range("AA34").Value
                              ' Skill Craft 9 Hours
                              A.Range("DB" & r).Value = B.Range("Q34").Value
                              ' Skill Craft 9 Total
                              A.Range("DC" & r).Value = B.Range("S34").Value
                             
                              ' Skill Craft 10
                              A.Range("DD" & r).Value = B.Range("D35").Value
                              ' Skill Craft 10 Start Date
                              A.Range("DE" & r).Value = B.Range("G35").Value
                              ' Skill Craft 10 Rate End Date
                              A.Range("DF" & r).Value = B.Range("I35").Value
                              ' Skill Craft 10 Quanity
                              A.Range("DG" & r).Value = B.Range("K35").Value
                              ' Skill Craft 10 Days
                              A.Range("DH" & r).Value = B.Range("L35").Value
                              ' Skill Craft 10 Hours
                              A.Range("DI" & r).Value = B.Range("M35").Value
                              ' Skill Craft 10 Straight Rate
                              A.Range("DJ" & r).Value = B.Range("Z35").Value
                              ' Skill Craft 10 Over Time Rate
                              A.Range("DK" & r).Value = B.Range("AA35").Value
                              ' Skill Craft 10 Hours
                              A.Range("DL" & r).Value = B.Range("Q35").Value
                              ' Skill Craft 10 Total
                              A.Range("DM" & r).Value = B.Range("S35").Value
                           
                           
                              ' Total SC Count
                              A.Range("DN" & r).Value = B.Range("O62").Value
                             
                            End If
                Exit For
            End If
        Next r
        Workbooks(OWB).Close SaveChanges:=False
        CWB.Activate
        ' Call GetUniqueValues
    End If
Next

Application.Calculation = xlAutomatic

End Sub
 
You must log in or register to reply here.
Back
Top