have some tables that give me information about different companies. When go to change catogry to see the other companies information it won't allow let me. It flashes up saying Run-Time Error 3024, couldn't find the file then gives me two options. to either Debug or End.<BR><BR>When i debug it brings up this information <BR><BR>'Dim wrkJet As Workspace<BR>Dim Dwh As Database<BR>Dim rs As Recordset<BR>Dim qstr As String<BR>Dim busn As String<BR>Dim fnd<BR>Dim cl As Range<BR>Dim name As String<BR>Dim BusinessArea As String<BR>Dim SupplyArea As String<BR>Dim Commodity As String<BR>Dim Supplier As String<BR>Dim ClientArea As String<BR>Dim ClaimType As String<BR>Dim q As String<BR>Dim Total As Double<BR>Dim rng2 As String<BR>Dim COffs As Integer<BR>Dim x As Integer<BR><BR>Function coloffs(Report As String)<BR>COffs = WorksheetFunction.HLookup(Report, Worksheets("Primary Data").Range("C4:IV56"), 53, False)<BR>coloffs = COffs<BR>End Function<BR><BR>Function supcoloffs(Report As String)<BR>COffs = WorksheetFunction.HLookup(Report, Worksheets("Supplier Primary Data").Range("C4:IV56"), 53, False)<BR>supcoloffs = COffs<BR>End Function<BR><BR>Sub Button38_Click()<BR><BR><BR><BR>Application.EnableCancelKey = xlDisabled<BR><BR>NewImport_Click<BR><BR>'UpdateMonth<BR><BR>'Application.Calculate<BR><BR>'HideMonths<BR><BR>'BuildDataSQL<BR><BR>'BuildSLASQL<BR><BR>'BuildTargetSQL<BR><BR>'DistributeData<BR><BR>'DistributeSLA<BR><BR>'DistributeTarget<BR><BR>'Application.Calculate<BR><BR>'HideMonths<BR><BR>'Application.Calculate<BR><BR>'ChangeAllCharts<BR><BR>'ChangeAllNewCharts<BR><BR>Application.StatusBar = False<BR><BR>End Sub<BR>Sub Import(ReportName As String)<BR><BR>x = coloffs(ReportName)<BR>rng2 = "A5:A52"<BR>q = ""<BR><BR>BusinessArea = Worksheets("Primary Data").Range("B58")<BR>SupplyArea = Worksheets("Primary Data").Range("B59")<BR>Commodity = Worksheets("Primary Data").Range("B60")<BR>Supplier = Worksheets("Primary Data").Range("B61")<BR>ClientArea = Worksheets("Primary Data").Range("B62")<BR>ClaimType = Worksheets("Primary Data").Range("B63")<BR><BR>If Not BusinessArea = "All" Then<BR>q = " AND BusinessSummary='" & BusinessArea & "'"<BR>End If<BR><BR>If Not SupplyArea = "All" Then<BR>q = q & " AND SupplyArea='" & SupplyArea & "'"<BR>End If<BR><BR>If Not Commodity = "All" Then<BR>q = q & " AND Commodity='" & Commodity & "'"<BR>End If<BR><BR>If Not Supplier = "All" Then<BR>q = q & " AND Supplier='" & Supplier & "'"<BR>End If<BR><BR>If Not ClientArea = "All" Then<BR>q = q & " AND ClientArea='" & ClientArea & "'"<BR>End If<BR><BR>If Not ClaimType = "All" Then<BR>q = q & " AND ClaimType='" & ClaimType & "'"<BR>End If<BR><BR>qstr = "Select ReportDate, SUM(DataValue) as Total from RawData where reportname='" & ReportName & "'" & q & " Group By ReportDate"<BR><BR>Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)<BR><BR>On Error GoTo altfile<BR>Set Dwh = wrkJet.OpenDatabase(ThisWorkbook.Path & "\DataWarehouseClient.mde")<BR>GoTo origfile<BR>altfile:<BR>Set Dwh = wrkJet.OpenDatabase(ThisWorkbook.Path & "\DataWarehouseClient.mdb")<BR>origfile:<BR>On Error GoTo 0<BR>Set rs = Dwh.OpenRecordset(qstr)<BR><BR>For Each cl In Worksheets("Primary Data").Range(rng2)<BR>cl.Offset(0, x) = 0<BR>cl.Offset(0, x) = Null<BR>Next<BR><BR>If rs.EOF = True Then<BR>'MsgBox "No records found."<BR>Exit Sub<BR>End If<BR><BR>With rs<BR>I = 4<BR><BR>Do<BR>name = Format(![ReportDate], "mmm-yy")<BR>If IsNull(![Total]) Then<BR>Total = 0<BR>Else<BR>Total = ![Total]<BR>End If<BR><BR>Set fnd = Worksheets("Primary Data").Range(rng2).Find(name, , xlValues)<BR>If Not fnd Is Nothing Then<BR><BR>ThisWorkbook.Worksheets("Primary Data").Range(fnd.Address).Offset(0, x).Value = Total<BR>If Total = 0 Then<BR>Total = Clear<BR>End If<BR>End If<BR>I = I + 1<BR>.MoveNext<BR>Loop Until .EOF = True<BR>End With<BR><BR>Dwh.Close<BR>wrkJet.Close<BR><BR>End Sub<BR><BR>Sub ImportSLA(ReportName As String)<BR><BR>x = coloffs(ReportName)<BR>rng2 = "A5:A52"<BR>q = ""<BR><BR>BusinessArea = Worksheets("Primary Data").Range("B58")<BR>SupplyArea = Worksheets("Primary Data").Range("B59")<BR>Commodity = Worksheets("Primary Data").Range("B60")<BR>Supplier = Worksheets("Primary Data").Range("B61")<BR>ClientArea = Worksheets("Primary Data").Range("B62")<BR>ClaimType = Worksheets("Primary Data").Range("B63")<BR><BR>If Not BusinessArea = "All" Then<BR>q = " AND BusinessSummary='" & BusinessArea & "'"<BR>End If<BR><BR>If Not SupplyArea = "All" Then<BR>q = q & " AND SupplyArea='" & SupplyArea & "'"<BR>End If<BR><BR>If Not Commodity = "All" Then<BR>q = q & " AND Commodity='" & Commodity & "'"<BR>End If<BR><BR>If Not Supplier = "All" Then<BR>q = q & " AND Supplier='" & Supplier & "'"<BR>End If<BR><BR>If Not ClientArea = "All" Then<BR>q = q & " AND ClientArea='" & ClientArea & "'"<BR>End If<BR><BR>If Not ClaimType = "All" Then<BR>q = q & " AND ClaimType='" & ClaimType & "'"<BR>End If<BR><BR>qstr = "Select ReportDate, Max(DataValue) as Total from RawData where reportname='" & ReportName & "'" & q & " Group By ReportDate"<BR><BR>Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)<BR><BR>On Error GoTo altfile<BR>Set Dwh = wrkJet.OpenDatabase(ThisWorkbook.Path & "\DataWarehouseClient.mde")<BR>GoTo origfile<BR>altfile:<BR>Set Dwh = wrkJet.OpenDatabase(ThisWorkbook.Path & "\DataWarehouseClient.mdb")<BR>origfile:<BR>On Error GoTo 0<BR>Set rs = Dwh.OpenRecordset(qstr)<BR><BR>For Each cl In Worksheets("Primary Data").Range(rng2)<BR>cl.Offset(0, x) = 0<BR>Next<BR><BR>If rs.EOF = True Then<BR>'MsgBox "No records found."<BR>Exit Sub<BR>End If<BR><BR>If IsNull(rs![Total]) Then<BR>Total = 0<BR>Else<BR>Total = rs![Total]<BR>End If<BR><BR>For Each cl In ThisWorkbook.Worksheets("Primary Data").Range("A5:A53")<BR>cl.Offset(0, x).Value = Total<BR>Next<BR><BR>Dwh.Close<BR>wrkJet.Close<BR><BR>End Sub<BR><BR>Sub ImportSLA2(ReportName As String)<BR>x = coloffs(ReportName)<BR>rng2 = "A5:A52"<BR>q = ""<BR><BR>BusinessArea = Worksheets("Primary Data").Range("B58")<BR>SupplyArea = Worksheets("Primary Data").Range("B59")<BR>Commodity = Worksheets("Primary Data").Range("B60")<BR>Supplier = Worksheets("Primary Data").Range("B61")<BR>ClientArea = Worksheets("Primary Data").Range("B62")<BR>ClaimType = Worksheets("Primary Data").Range("B63")<BR><BR>If Not BusinessArea = "All" Then<BR>q = " AND BusinessSummary='" & BusinessArea & "'"<BR>End If<BR><BR>If Not SupplyArea = "All" Then<BR>q = q & " AND SupplyArea='" & SupplyArea & "'"<BR>End If<BR><BR>If Not Commodity = "All" Then<BR>q = q & " AND Commodity='" & Commodity & "'"<BR>End If<BR><BR>If Not Supplier = "All" Then<BR>q = q & " AND Supplier='" & Supplier & "'"<BR>End If<BR><BR>If Not ClientArea = "All" Then<BR>q = q & " AND ClientArea='" & ClientArea & "'"<BR>End If<BR><BR>If Not ClaimType = "All" Then<BR>q = q & " AND ClaimType='" & ClaimType & "'"<BR>End If<BR><BR><BR>qstr = "Select ReportDate, Avg(DataValue) as Total from RawData where reportname='" & ReportName & "'" & q & " Group By ReportDate"<BR><BR>Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)<BR><BR>On Error GoTo altfile<BR>Set Dwh = wrkJet.OpenDatabase(ThisWorkbook.Path & "\DataWarehouseClient.mde")<BR>GoTo origfile<BR>altfile:<BR>Set Dwh = wrkJet.OpenDatabase(ThisWorkbook.Path & "\DataWarehouseClient.mdb")<BR>origfile:<BR>On Error GoTo 0<BR>Set rs = Dwh.OpenRecordset(qstr)<BR><BR>For Each cl In Worksheets("Primary Data").Range(rng2)<BR>cl.Offset(0, x) = 0<BR>Next<BR><BR>If rs.EOF = True Then<BR>'MsgBox "No records found."<BR>Exit Sub<BR>End If<BR><BR>If IsNull(rs![Total]) Then<BR>Total = 0<BR>Else<BR>Total = rs![Total]<BR>End If<BR><BR>For Each cl In ThisWorkbook.Worksheets("Primary Data").Range("A5:A53")<BR>cl.Offset(0, x).Value = Total<BR>Next<BR><BR>Dwh.Close<BR>wrkJet.Close<BR>End Sub<BR>Sub UpdateMonth()<BR>Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)<BR><BR>On Error GoTo altfile<BR>Set Dwh = wrkJet.OpenDatabase(ThisWorkbook.Path & "\DataWarehouseClient.mde")<BR>GoTo origfile<BR>altfile:<BR>Set Dwh = wrkJet.OpenDatabase(ThisWorkbook.Path & "\DataWarehouseClient.mdb")<BR>origfile:<BR>On Error GoTo 0<BR>Set rs = Dwh.OpenRecordset("Select CurrentPeriodEnd FROM SystemData")<BR><BR>ThisWorkbook.Worksheets("Lookups").Range("K2") = rs!CurrentPeriodEnd<BR><BR><BR>End Sub'<BR><BR>What does this mean? and how can i fix this?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
