Update Data Connection of Existing Worksheets

[Bass99]

Hello Everyone,

Thank you for all the help you provide here. I have basic knowledge of working with VBA, I understand how it works and I can follow instructions and apply changes, but I have no experience. I am currently using Excel 2013.

This project I am working on is a workbook that contains a summary worksheet “DailyAnalysis” and about 117 additional worksheets related to stock symbols (additional stock symbols will be added). The summary worksheet “DailyAnalysis” contains a list of stock symbols in column “C” and the other columns contain additional formulas and analytical data.

I have the workbook set up, based on resources from: msdn.microsoft.com, investexcel.net and MrExcel to name a few, as follows:

1. A workbook to download End of Day stock information and save a .CSV copy for every stock symbol with stock symbol name in C:\StockEOD\ folder.
2. A macro to add hyperlink to each stock symbol in column “C”.
3. A macro to add a new worksheet for every new stock symbol added to the list, based on a worksheet named “AA” I use this worksheet as a template. The macro will search the list, skip already existing worksheets with stock symbol, if the stock symbol doesn’t have an assigned worksheet, a new copy of “AA” will be created “AA1” and renamed accordingly.
4. A macro to arrange the worksheets alphabetically.
5. Each worksheet has a Data Connection to retrieve End of Day stock information from its assigned .CSV file in C:\StockEOD\. The EOD information will be inserted in cell A7 to H7 down the reset of the worksheet contains additional formulas and analytical data.

At this point I am trying to have a macro to update the data connection of the newly created worksheet (since it is based on “AA” worksheet connection) points to the right .CSV file in C:\StockEOD\ based on the worksheets name. The following VBA adds a new worksheet and creates a connection.
****************

This is the VBA that creates a new worksheet:


Sub CreateSheetsFromList()
Application.ScreenUpdating = False
Dim symbolCell As Range, Symbol As Range
'Dim ws As Worksheet

Set Symbol = Sheets("DailyAnalysis").Range("C2")
Set Symbol = Range(Symbol, Symbol.End(xlDown))

For Each symbolCell In Symbol
On Error Resume Next
If IsError(Worksheets(symbolCell.Value)) Then
Sheets("AA").Select ' Select the templete worksheet "AA" as the new worksheet to create
Sheets("AA").Copy After:=Sheets(Sheets.Count) 'Creates a new worksheet based on the AA. Worksheet will be named AA(1), (2) ect....
Sheets(Sheets.Count).Name = symbolCell.Value ' Renames the worksheets from the DailyAnalysis of symbols.
Range("C2").Select ' Select cell with symbol ticker
Selection.Copy 'Copy cell with symbol ticker
Range("C3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False ' Paste as a value the symbol ticker in C3
Application.CutCopyMode = False ' Deselect the copy
Range("A1").Select
End If

Next symbolCell
Application.ScreenUpdating = True
Sheets("DailyAnalysis").Select
End Sub
***************


This is the VBA I am trying to use to update the data connection for the newly created worksheet using the VBA above.

Sub UpdateDataConnectionofExistingWorksheets()
Dim strFile As String
Dim ws As Worksheet
strFile = Dir("c:\StockEOD\*.csv")
Do While strFile <> vbNullString
Set ws = Sheets.Add
With ws.QueryTables.Add(Connection:= _
"TEXT;" & "C:\StockEOD\" & strFile, Destination:=Range("$A$7"))
.Name = strFile
.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 = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
strFile = Dir
Loop
End Sub
****************


Thanks for your help