Don't save external data.
 

Hi, I have a workbook that imports data from a folder containing .csv
files and puts the data from each csv file into a new sheet. What I
want to do is save the workbook and retain the sheets and the code that
fetches the external data, but not actually save the external data. Is
that possible? Here's what I have right now:

Code to create worksheets and import data from csv files. Note I use a
list of filenames in column B starting on row 6 on sheet "Files"

Function GetData()
Dim rng As Range, i As Long
Dim symbol As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
i = 6
Set rng = Cells(i, 2)
Do While Application.CountA(rng.Resize(1, 15)) < 0
symbol = Cells(i, 2).Value
Call import(symbol)
i = i + 1
Set rng = rng.Offset(1, 0)
Loop
End Function

Sub import(symbol)
Dim savepath As String
ActiveWorkbook.Worksheets.Add
ActiveSheet.Name = symbol
savepath = ThisWorkbook.path & "\Data\" & symbol & ".csv"
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" &
savepath, _
Destination:=Range("A1"))
.Name = symbol
.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, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("Files").Select
End Sub

I know I'll need to remove the code that creates new sheets, as I want
to save the created sheets (but not the external data thats in them).

Don't save external data.
 

I guess there is a reason you want to bring in the data even though you
don't want to save it (otherwise, you wouldn't need to bring it in)

Function GetData()
Dim rng As Range, i As Long
Dim symbol As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
i = 6
Set rng = Cells(i, 2)
Do While Application.CountA(rng.Resize(1, 15)) < 0
symbol = Cells(i, 2).Value
Call import(symbol)
i = i + 1
Set rng = rng.Offset(1, 0)
Loop

' do something with the sheets

i = 6
Set rng = Cells(i, 2)
Do While Application.CountA(rng.Resize(1, 15)) < 0
symbol = Cells(i, 2).Value
worksheets(symbol).Cells.Clear
i = i + 1
Set rng = rng.Offset(1, 0)
Loop
End Function

--
Regards,
Tom Ogilvy

"Nick" wrote in message
ups.com...
Hi, I have a workbook that imports data from a folder containing .csv
files and puts the data from each csv file into a new sheet. What I
want to do is save the workbook and retain the sheets and the code that
fetches the external data, but not actually save the external data. Is
that possible? Here's what I have right now:

Code to create worksheets and import data from csv files. Note I use a
list of filenames in column B starting on row 6 on sheet "Files"

Function GetData()
Dim rng As Range, i As Long
Dim symbol As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
i = 6
Set rng = Cells(i, 2)
Do While Application.CountA(rng.Resize(1, 15)) < 0
symbol = Cells(i, 2).Value
Call import(symbol)
i = i + 1
Set rng = rng.Offset(1, 0)
Loop
End Function

Sub import(symbol)
Dim savepath As String
ActiveWorkbook.Worksheets.Add
ActiveSheet.Name = symbol
savepath = ThisWorkbook.path & "\Data\" & symbol & ".csv"
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" &
savepath, _
Destination:=Range("A1"))
.Name = symbol
.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, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("Files").Select
End Sub

I know I'll need to remove the code that creates new sheets, as I want
to save the created sheets (but not the external data thats in them).

Don't save external data.
 

Okay, thanks. Problem with that is that I've put other data in those
sheets that isn't from the csv file that I want to keep, and
worksheets(symbol).Cells.Clear would delete it. I've found another way
I think:

Dim rng As Range, i As Long
Dim symbol As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
i = 6
Set rng = Cells(i, 2)
Do While Application.CountA(rng.Resize(1, 15)) < 0
symbol = Cells(i, 2).Value
Sheets(symbol).Select
Application.Goto Reference:=symbol
Selection.ClearContents
Sheets("Stocks").Select
i = i + 1
Set rng = rng.Offset(1, 0)
Loop

Don't save external data.
 

You have the advantage of knowing what you want to do. <g

--
Regards,
Tom Ogilvy


"Nick" wrote in message
oups.com...
Okay, thanks. Problem with that is that I've put other data in those
sheets that isn't from the csv file that I want to keep, and
worksheets(symbol).Cells.Clear would delete it. I've found another way
I think:

Dim rng As Range, i As Long
Dim symbol As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
i = 6
Set rng = Cells(i, 2)
Do While Application.CountA(rng.Resize(1, 15)) < 0
symbol = Cells(i, 2).Value
Sheets(symbol).Select
Application.Goto Reference:=symbol
Selection.ClearContents
Sheets("Stocks").Select
i = i + 1
Set rng = rng.Offset(1, 0)
Loop