Run Time Error 3024

A

Amii

New Member
Joined
Aug 21, 2012
Messages
5
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.

When i debug it brings up this information

'Dim wrkJet As Workspace
Dim Dwh As Database
Dim rs As Recordset
Dim qstr As String
Dim busn As String
Dim fnd
Dim cl As Range
Dim name As String
Dim BusinessArea As String
Dim SupplyArea As String
Dim Commodity As String
Dim Supplier As String
Dim ClientArea As String
Dim ClaimType As String
Dim q As String
Dim Total As Double
Dim rng2 As String
Dim COffs As Integer
Dim x As Integer

Function coloffs(Report As String)
COffs = WorksheetFunction.HLookup(Report, Worksheets("Primary Data").Range("C4:IV56"), 53, False)
coloffs = COffs
End Function

Function supcoloffs(Report As String)
COffs = WorksheetFunction.HLookup(Report, Worksheets("Supplier Primary Data").Range("C4:IV56"), 53, False)
supcoloffs = COffs
End Function

Sub Button38_Click()



Application.EnableCancelKey = xlDisabled

NewImport_Click

'UpdateMonth

'Application.Calculate

'HideMonths

'BuildDataSQL

'BuildSLASQL

'BuildTargetSQL

'DistributeData

'DistributeSLA

'DistributeTarget

'Application.Calculate

'HideMonths

'Application.Calculate

'ChangeAllCharts

'ChangeAllNewCharts

Application.StatusBar = False

End Sub
Sub Import(ReportName As String)

x = coloffs(ReportName)
rng2 = "A5:A52"
q = ""

BusinessArea = Worksheets("Primary Data").Range("B58")
SupplyArea = Worksheets("Primary Data").Range("B59")
Commodity = Worksheets("Primary Data").Range("B60")
Supplier = Worksheets("Primary Data").Range("B61")
ClientArea = Worksheets("Primary Data").Range("B62")
ClaimType = Worksheets("Primary Data").Range("B63")

If Not BusinessArea = "All" Then
q = " AND BusinessSummary='" & BusinessArea & "'"
End If

If Not SupplyArea = "All" Then
q = q & " AND SupplyArea='" & SupplyArea & "'"
End If

If Not Commodity = "All" Then
q = q & " AND Commodity='" & Commodity & "'"
End If

If Not Supplier = "All" Then
q = q & " AND Supplier='" & Supplier & "'"
End If

If Not ClientArea = "All" Then
q = q & " AND ClientArea='" & ClientArea & "'"
End If

If Not ClaimType = "All" Then
q = q & " AND ClaimType='" & ClaimType & "'"
End If

qstr = "Select ReportDate, SUM(DataValue) as Total from RawData where reportname='" & ReportName & "'" & q & " Group By ReportDate"

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)

On Error GoTo altfile
Set Dwh = wrkJet.OpenDatabase(ThisWorkbook.Path & "\DataWarehouseClient.mde")
GoTo origfile
altfile:
Set Dwh = wrkJet.OpenDatabase(ThisWorkbook.Path & "\DataWarehouseClient.mdb")
origfile:
On Error GoTo 0
Set rs = Dwh.OpenRecordset(qstr)

For Each cl In Worksheets("Primary Data").Range(rng2)
cl.Offset(0, x) = 0
cl.Offset(0, x) = Null
Next

If rs.EOF = True Then
'MsgBox "No records found."
Exit Sub
End If

With rs
I = 4

Do
name = Format(![ReportDate], "mmm-yy")
If IsNull(![Total]) Then
Total = 0
Else
Total = ![Total]
End If

Set fnd = Worksheets("Primary Data").Range(rng2).Find(name, , xlValues)
If Not fnd Is Nothing Then

ThisWorkbook.Worksheets("Primary Data").Range(fnd.Address).Offset(0, x).Value = Total
If Total = 0 Then
Total = Clear
End If
End If
I = I + 1
.MoveNext
Loop Until .EOF = True
End With

Dwh.Close
wrkJet.Close

End Sub

Sub ImportSLA(ReportName As String)

x = coloffs(ReportName)
rng2 = "A5:A52"
q = ""

BusinessArea = Worksheets("Primary Data").Range("B58")
SupplyArea = Worksheets("Primary Data").Range("B59")
Commodity = Worksheets("Primary Data").Range("B60")
Supplier = Worksheets("Primary Data").Range("B61")
ClientArea = Worksheets("Primary Data").Range("B62")
ClaimType = Worksheets("Primary Data").Range("B63")

If Not BusinessArea = "All" Then
q = " AND BusinessSummary='" & BusinessArea & "'"
End If

If Not SupplyArea = "All" Then
q = q & " AND SupplyArea='" & SupplyArea & "'"
End If

If Not Commodity = "All" Then
q = q & " AND Commodity='" & Commodity & "'"
End If

If Not Supplier = "All" Then
q = q & " AND Supplier='" & Supplier & "'"
End If

If Not ClientArea = "All" Then
q = q & " AND ClientArea='" & ClientArea & "'"
End If

If Not ClaimType = "All" Then
q = q & " AND ClaimType='" & ClaimType & "'"
End If

qstr = "Select ReportDate, Max(DataValue) as Total from RawData where reportname='" & ReportName & "'" & q & " Group By ReportDate"

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)

On Error GoTo altfile
Set Dwh = wrkJet.OpenDatabase(ThisWorkbook.Path & "\DataWarehouseClient.mde")
GoTo origfile
altfile:
Set Dwh = wrkJet.OpenDatabase(ThisWorkbook.Path & "\DataWarehouseClient.mdb")
origfile:
On Error GoTo 0
Set rs = Dwh.OpenRecordset(qstr)

For Each cl In Worksheets("Primary Data").Range(rng2)
cl.Offset(0, x) = 0
Next

If rs.EOF = True Then
'MsgBox "No records found."
Exit Sub
End If

If IsNull(rs![Total]) Then
Total = 0
Else
Total = rs![Total]
End If

For Each cl In ThisWorkbook.Worksheets("Primary Data").Range("A5:A53")
cl.Offset(0, x).Value = Total
Next

Dwh.Close
wrkJet.Close

End Sub

Sub ImportSLA2(ReportName As String)
x = coloffs(ReportName)
rng2 = "A5:A52"
q = ""

BusinessArea = Worksheets("Primary Data").Range("B58")
SupplyArea = Worksheets("Primary Data").Range("B59")
Commodity = Worksheets("Primary Data").Range("B60")
Supplier = Worksheets("Primary Data").Range("B61")
ClientArea = Worksheets("Primary Data").Range("B62")
ClaimType = Worksheets("Primary Data").Range("B63")

If Not BusinessArea = "All" Then
q = " AND BusinessSummary='" & BusinessArea & "'"
End If

If Not SupplyArea = "All" Then
q = q & " AND SupplyArea='" & SupplyArea & "'"
End If

If Not Commodity = "All" Then
q = q & " AND Commodity='" & Commodity & "'"
End If

If Not Supplier = "All" Then
q = q & " AND Supplier='" & Supplier & "'"
End If

If Not ClientArea = "All" Then
q = q & " AND ClientArea='" & ClientArea & "'"
End If

If Not ClaimType = "All" Then
q = q & " AND ClaimType='" & ClaimType & "'"
End If


qstr = "Select ReportDate, Avg(DataValue) as Total from RawData where reportname='" & ReportName & "'" & q & " Group By ReportDate"

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)

On Error GoTo altfile
Set Dwh = wrkJet.OpenDatabase(ThisWorkbook.Path & "\DataWarehouseClient.mde")
GoTo origfile
altfile:
Set Dwh = wrkJet.OpenDatabase(ThisWorkbook.Path & "\DataWarehouseClient.mdb")
origfile:
On Error GoTo 0
Set rs = Dwh.OpenRecordset(qstr)

For Each cl In Worksheets("Primary Data").Range(rng2)
cl.Offset(0, x) = 0
Next

If rs.EOF = True Then
'MsgBox "No records found."
Exit Sub
End If

If IsNull(rs![Total]) Then
Total = 0
Else
Total = rs![Total]
End If

For Each cl In ThisWorkbook.Worksheets("Primary Data").Range("A5:A53")
cl.Offset(0, x).Value = Total
Next

Dwh.Close
wrkJet.Close
End Sub
Sub UpdateMonth()
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)

On Error GoTo altfile
Set Dwh = wrkJet.OpenDatabase(ThisWorkbook.Path & "\DataWarehouseClient.mde")
GoTo origfile
altfile:
Set Dwh = wrkJet.OpenDatabase(ThisWorkbook.Path & "\DataWarehouseClient.mdb")
origfile:
On Error GoTo 0
Set rs = Dwh.OpenRecordset("Select CurrentPeriodEnd FROM SystemData")

ThisWorkbook.Worksheets("Lookups").Range("K2") = rs!CurrentPeriodEnd


End Sub'

What does this mean? and how can i fix this?
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Excel cannot find the file its being told to use. From what I see, either "DataWarehouseClient.mde" or "DataWarehouseClient.mdb"
When you click debug and the above code appears, there should also be a line hi-lited which caused the error.
This is probably a line with a file name. Probably the missing one.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
VBA Run-time error ‘438’ - Unknown Reason
Replies
2
Views
853
rlv01
R
S
  • Question
Run-time error '1004': Method 'SaveAs' of object'_Workbook' failed
Replies
5
Views
3K
Joe4
Joe4
M
  • Question
Run-time error ‘91’: Object variable or With block variable not set
Replies
3
Views
5K
Joe4
Joe4
W
  • Question
Run-Time Error 380 invalid property value
Replies
3
Views
3K
NdNoviceHlp
NdNoviceHlp
M
  • Question
Different results running same macro with same data
Replies
3
Views
2K
Logit
Logit

Forum statistics

Threads
1,214,784
Messages
6,121,535
Members
449,037
Latest member
tmmotairi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top