Unrecognized path on 2nd run of VBA code

[mgilliland78]

I've created a spreadsheet to allow some management to track certain quality assurance activities their employees are performing right now. The users all have a spreadsheet with a checklist on it and a couple of macros that generate an email and back the data up into a text file per user.

I created a companion file with a macro that collects all of the text files, creates a consolidated text file and imports it all into Excel. This is all stored on a network drive. The problem I'm having is this... I can only successfully run the macro once, before I get an error with the network path, followed by Run-time error 1004 for the network path.

I've tested the path in Explorer and as a shortcut on my desktop. The same file's macro also works flawlessly multiple times on other PCs, just not on mine. Please HELP!

Here's my code:
Sub Consolidate_QA_Dbl_Edit_Data()


'Deletes any old "Consolidated.txt" Files that will cause runtime errors
On Error Resume Next
Kill "\\rosemontfs\COEShares\SHARED\Mike\QADBLEdit\Consolidated\Consolidated.txt"
On Error GoTo 0


'Consolidates the data into a single file, located in a subfolder
Shell Environ$("COMSPEC") & " /c Copy \\rosemontfs\COEShares\SHARED\Mike\QADBLEdit\*.txt \\rosemontfs\COEShares\SHARED\Mike\QADBLEdit\Consolidated\Consolidated.txt"


'Deletes the Button and black-fill from the file
ActiveSheet.Shapes.Range(Array("Button 1")).Select
Selection.Delete
Cells.Select
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=-6
Range("A1").Select


'Imports the data into Excel
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;\\rosemontfs\COEShares\SHARED\Mike\QADBLEdit\Consolidated\Consolidated.txt" _
, Destination:=Range("$A$1"))
.Name = "Consolidated 2015_01_08_11_23"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False 'THIS IS THE LINE DEBUG SAYS IS PRODUCING THE RUNTIME ERROR
End With


'Imports the data into Excel - New Version
'Set TextFile = Workbooks.Open("\\rosemontfs\COEShares\SHARED\Mike\QADBLEdit\Consolidated\Consolidated.txt")
' TextFile.Sheets(1).Range("A1").Current Region.copy_
' ThisWorkbook.Sheets(1).Range ("A1")
' TextFile.Close (False)




'Adds time/date stamp to the consolidated datafile
dt = Format(CStr(Now), "yyyy_mm_dd_hh_mm_ss")
GivenLocation = "\\rosemontfs\COEShares\SHARED\Mike\QADBLEdit\Consolidated\"
OldFileName = "Consolidated.txt"
NewFileName = "Consolidated " & dt & ".txt"
Name GivenLocation & OldFileName As GivenLocation & NewFileName


'Formats the imported data
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
Range("A1").Select


'Saves file as GT Double Edit Checklists
Application.DisplayAlerts = False
Dim SaveasFilename As String
SaveasFilename = "\\rosemontfs\COEShares\SHARED\Mike\QADBLEdit\Consolidated\GT Double Edit Data"
ActiveWorkbook.SaveAs SaveasFilename & " " & dt, FileFormat:=51
Application.DisplayAlerts = True




End Sub

 

[Blade Hunter]

Do you have a missing reference? Tools References in the VBE

 

[mgilliland78]

No missing references that I could identify. I don't think that's the problem, though, or it should affect others the same way it affects me. I'm the only one with an issue on the same file that 7 other people are using.

Since the debug showed .Refresh BackgroundQuery:=False as the source of the error, I decided to try switching it to True. This solved my problem, but only for a single execution again. Subsequent runs of the macro still produced the run-time error.

I worked around the issue by splitting the With statement out into it's own subroutine twice, once with True and once with False as the refresh options. Then I inserted the following code:
'Imports the data into Excel
' Default Code
Application.DisplayAlerts = False
On Error Resume Next
Call ImportCode1
' Backup/Alternate Code
On Error Resume Next
Call ImportCode2
Application.DisplayAlerts = True


By alternating the option and suppressing the run-time errors, I now have a working macro. However, I'm not sure why I was getting the error in the first place and that's what I really need to know! Otherwise, I could have similarly nonsensical errors with other functions.

 

[AkaTrouble]

un-educated guess here ...

you are opening a connection to the network drive maybe if this connection is still open this is the error on trying to open it second time

i note you have kill directory at begining but nothing closing the connection

like i say un-educated guess