Run-time error '1004'

swell

New Member
Joined
Oct 7, 2019
Messages
4
I have a monthly report which has a macro that combines data from two sources automatically and updates several pivot tables.

This report used to work flawless on ex coworkers computer. He sent me the report and now I cannot make it work. I receive below error:

Run-time error '1004' :
We couldn't refresh the connection 'Query- Monthly Reporting'. Here's the error message we got:
[DataSource.Not Found] File or Folder: We couldn't find the folder
'C:\Users\John\Desktop\Monthly Reporting'.

And when I clicked on Debug, on VBA editor it shows below line to fix:

Code:
[COLOR=#333333]If con.Name = "Query - MONTHLY REPORTING" Then con.Refresh[/COLOR]

Feels like it would be a simple fix but since I've never played with macros, I cannot find out what the problem is.

 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sounds more like the file / folder has either been moved, or has been renamed !
 
Upvote 0
Hi Michael,

Yes it did. We just moved the whole folder to my computer and thought that it should work fine just as it was in my coworker's. I've changed the data source in Power Query editor and hoped that it should work fine but no luck.
 
Upvote 0
Is the drive correct AND path EXACTLY the same...still 'C:\Users\John\Desktop\Monthly Reporting'.
you might have to post the entire code !!
 
Last edited:
Upvote 0
Sure, sorry for not putting whole code. When I click on Debug, it takes me to the VBA editor and here's the whole code:

Code:
Sub ReceivedCases()
Application.ScreenUpdating = False
ufProgress.LabelProgress.Width = 0
ufProgress.Show
FractionComplete (0)




Dim wkm As Workbook
Dim wkarch As Workbook
Dim wkPiv As Workbook
Dim Fnamem As String
Dim Fnamearch As String
Dim FPath As String
Dim LanID As String
Dim qt As WorkbookQuery
Dim pt As PivotTable
Dim X As Boolean




Dim lRow As Long
Dim lRowl As Long


LanID = Environ("Username")
Shell "C:\Users\" & LanID & "\Desktop\MONTHLY REPORTING\Received Cases\ChangeNames.bat", vbNormalFocus


FPath = "C:\Users\" & LanID & "\Desktop\MONTHLY REPORTING\Received Cases\"
Fnamem = FPath & "Received This Month.xlsx"
Fnamearch = FPath & "Archive Raw Data.xlsx"


Application.ScreenUpdating = False
Set wkm = Workbooks.Open(Fnamem)
wkm.Activate




''wkm.Connections("MONTHLY REPORTING").Refresh BackgroundQuery:=False
''wkm.Connections("Query - Last Month Combined").Refresh BackgroundQuery:=False
''For Each con In wkm.Connections
    ''Set con = wkm.Connections
    ''If con.Name = "Query - RAD REPORTING" Then
      ''  con.Refresh
    ''Else
      ''  MsgBox ("Check OUt connection Name")
    ''End If
FractionComplete (0.25)
Application.ScreenUpdating = False
'';Set qt = wkm.workbookqueries


''qt("MONTHLY REPORTING").Refresh


Dim con As WorkbookConnection
Dim lCnt As Long


    'The following code loops through all connections
    'in the active workbook.  Change the property to
    'True to Enable, False to Disable background refresh.
    
    With wkm
        For lCnt = 1 To .Connections.Count
          'Excludes PowerPivot and other connections
          If .Connections(lCnt).Type = xlConnectionTypeOLEDB Then
            .Connections(lCnt).OLEDBConnection.BackgroundQuery = False
          End If
        Next lCnt
    End With
''OLEDBConnection.BackgroundQuery = False




For Each con In wkm.Connections
    
    If con.Name = "Query - MONTHLY REPORTING" Then [COLOR=#0000ff]con.Refresh[/COLOR]
    
Next


 
For Each con In wkm.Connections
    If con.Name = "Query - MONTHLY REPORTING (2)" Then con.Refresh
Next
FractionComplete (0.5)
Application.ScreenUpdating = False


    For Each con In wkm.Connections
    If con.Name = "Query - Last Month Combined" Then con.Refresh
Next
''wkm.Sheets(1).ListObjects("Last Month Combined").Refresh BackgroundQuery:=False


wkm.Save




Set wkarch = Workbooks.Open(Fnamearch)


wkm.Activate






lRow = Cells(Rows.Count, 1).End(xlUp).Row
wkm.Worksheets(1).Range("A2:AE" & lRow).Copy Destination:=wkarch.Worksheets(2).Range("A" & wkarch.Worksheets(2).Range("A65536").End(xlUp).Row)
''wkarch.Worksheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
Application.CutCopyMode = False
FractionComplete (0.75)
Application.ScreenUpdating = False
wkm.Save
wkm.Close
wkarch.Save
wkarch.Close


''ThisWorkbook.Activate
''For Each qt In ThisWorkbook.QueryTables
''qt.Refresh BackgroundQuery:=False
''Next qt


''For Each pt In ThisWorkbook.PivotTables
''pt.Refresh
''Next pt


 With ThisWorkbook
        For lCnt = 1 To .Connections.Count
          'Excludes PowerPivot and other connections
          If .Connections(lCnt).Type = xlConnectionTypeOLEDB Then
            .Connections(lCnt).OLEDBConnection.BackgroundQuery = False
          End If
        Next lCnt
    End With
    
For Each con In ThisWorkbook.Connections
    
    If con.Name = "Query - Table1" Then con.Refresh
    
Next
    ThisWorkbook.RefreshAll
    MsgBox ("Pivots were Updated")


FractionComplete (1)
Unload ufProgress
Application.ScreenUpdating = True
End Sub


Sub FractionComplete(pctdone As Single)
With ufProgress
    .LabelCaption.Caption = pctdone * 100 & "% Complete"
    .LabelProgress.Width = pctdone * (.FrameProgress.Width)
End With
DoEvents
End Sub
 
Upvote 0
Forgot to mention. VBA editor pointing out that this is the line I should edit:

If con.Name = "Query - RAD REPORTING" Then con.Refresh
 
Upvote 0
Does the one further down work ???

Code:
For Each con In wkm.Connections
    
    If con.Name = "Query - MONTHLY REPORTING" Then con.Refresh
    
Next
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

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