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
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