Importing .csv file once daily leaves a data connection

S

Saucer Man

I have a macro that imports a .csv file into Excel. This macro is run once
at night. I recently saw under Connections that each time we do an import,
it leaves a connection. So after importing the file for 3 nights, we have 3
connections. I don't believe I need these connections to remain. Each
night is a fresh import into different rows so we are not actually
refreshing the previous connection. How can I prevent these connections
from remaining after the import?
 
S

Saucer Man

Here it is without the declares...

'Set rng = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
'With
ActiveSheet.QueryTables.Add(Connection:="TEXT;\\Data-store\UPS_CSV_EXPORT.csv",
Destination:=rng)
' .Name = "UPS_CSV_EXPORT"
' .FieldNames = True
' .RowNumbers = False
' .FillAdjacentFormulas = False
' .PreserveFormatting = True
' .RefreshOnFileOpen = False
' .RefreshStyle = xlInsertDeleteCells
' .SavePassword = False
' .SaveData = True
' .AdjustColumnWidth = False
' .RefreshPeriod = 0
' .TextFilePromptOnRefresh = False
' .TextFilePlatform = 437
' .TextFileStartRow = 1
' .TextFileParseType = xlDelimited
' .TextFileTextQualifier = xlTextQualifierDoubleQuote
' .TextFileConsecutiveDelimiter = False
' .TextFileTabDelimiter = False
' .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)
' .TextFileTrailingMinusNumbers = True
' .Refresh BackgroundQuery:=False
'End With
'Set rng = Nothing
 
S

Saucer Man

I tried adding...

ActiveWorkbook.Connections("UPS_CSV_EXPORT").Delete

and the connection is gone when viewed in the connections screen in Excel
2007. However, the next time I open the Excel document, the connection is
back again.

Also, the connections created when we run the macro daily are building up.
There is

UPS_CSV_EXPORT1
UPS_CSV_EXPORT2
UPS_CSV_EXPORT3
UPS_CSV_EXPORT4
UPS_CSV_EXPORT5
UPS_CSV_EXPORT6

Even removing these with the Connections dialog in Excel 2007 is only
temporary. They come back when we open the document the next time.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top