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.

 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,199
Office Version
2013
Platform
Windows
Sounds more like the file / folder has either been moved, or has been renamed !
 

swell

New Member
Joined
Oct 7, 2019
Messages
4
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.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,199
Office Version
2013
Platform
Windows
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:

swell

New Member
Joined
Oct 7, 2019
Messages
4
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
 

swell

New Member
Joined
Oct 7, 2019
Messages
4
Forgot to mention. VBA editor pointing out that this is the line I should edit:

If con.Name = "Query - RAD REPORTING" Then con.Refresh
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,199
Office Version
2013
Platform
Windows
Does the one further down work ???

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

Forum statistics

Threads
1,082,305
Messages
5,364,401
Members
400,800
Latest member
germanpbv

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top