PDA

View Full Version : [SOLVED] Excel data import once only and save as new file



PJGallagher
08-28-2013, 03:34 AM
Hi all,

Newbie to Excel & VBA and and a few questions please...

I'm looking to have an Excel MASTER.xls file, that when opened;

1. imports data from a bunch of .csv files from a known folder (.\data\) - this happens ok.
2. charts are auto populated with data just imported (the range is static) - this happens ok.
3. the file CANT be saved back over the master file, so saved as "new1example.xls" - how to enforce that the MASTER file cant be over-written?
4. on emailing to colleague the "new1example.xls", I don't want the file when opened to try and reimport or reference the source data (as it will be stored on a seperate server that the colleague will not have access to) - cant get this to work!? Error message about being unable to find text file to refresh this external data range?!

What I have so far is a MASTER.xls file with a few functions defined as per below, a data folder (.\data\) which contains the .csv files with the data in;

1. ImportCSVData "Data1.csv", "WSData1"

Sub ImportCSVData(strFileName As String, strDestSheet As String)
Dim strConn As String
strConn = "TEXT;" & ThisWorkbook.Path & "\data\" & strFileName
With Worksheets(strDestSheet).QueryTables.Add(Connection:=strConn, Destination:=Worksheets(strDestSheet).Range("A1"))
.Name = strFileName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.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
.Refresh BackgroundQuery:=False
End With
End Sub



Any help greatly appreciated

Thanks

Paul

PJGallagher
08-28-2013, 04:15 AM
Have solved number 3 above by using below code, so just number 4 to fix... How to stop the emailled file trying to reference external data source when file is opened.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim strFileName As String
Const strRestrictedName As String = "MASTER_v1.0.xls"
Application.EnableEvents = False
Cancel = True
strFileName = Application.GetSaveAsFilename(fileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls")
strFileName = Mid$(strFileName, InStrRev(strFileName, "\") + 1)
If UCase$(strFileName) = UCase$(strRestrictedName) Then
MsgBox "Invalid File Name!" & vbCrLf & vbCrLf & "MASTER file cannot be overwritten", vbCritical, "Stop"
Else
ActiveWorkbook.SaveAs strFileName
End If
Application.EnableEvents = True
End Sub

SamT
08-28-2013, 10:11 AM
Are you sure that the Charts are only using static ranges internal to Workbook?

If the external reference is on a Worksheet, then

WkSht.Cells.Copy
WkSht.Cells.PasteSpecial(xlPasteValues)