Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another Reference Problem
This is part of a macro that draws a chart. I need to fix this code so that
it will reference the named worksheet, clear the chart worksheet, name the chart tab with: Chart(samenameasworksheet). I also need it to clear the chart worksheet and tab name, so if the macro is run a second time, it will use the same chart worksheet. If you look at line 4 of the chart code, =Sheets refers to "AAA" which is one filename out of a possible 5,000 or more files. I tried referring to mydatafile, which is what is used in the macro part that gets the data and filename in the first place, but this did not work. I'm only a beginner with VBA and put most of this together using snippets of code found on this news groups and by using the macro-recorder. Where I ALWAYS seem to get stuck is modifying the path that is referenced, so if some kind soul out there would like to give me or point to a tutorial that could explain this, I'd be forever grateful. Thanks. I also included part of the first portion of the macro that opens the worksheet and names the tab with the .txt filename less the .txt extension; but first here is the code that draws the chart below: Range("A1:A50,E1:E50,H1:H50").Select Charts.Add ActiveChart.ChartType = xlLine ActiveChart.SetSourceData Source:=Sheets("AAA").Range("A1:A50,E1:E50,H1:H50" ) _ , PlotBy:=xlColumns ActiveChart.Location Whe=xlLocationAsNewSheet With ActiveChart .HasAxis(xlCategory, xlPrimary) = True .HasAxis(xlValue, xlPrimary) = True End With ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(1).AxisGroup = 2 With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With With Selection .MarkerBackgroundColorIndex = xlAutomatic .MarkerForegroundColorIndex = xlAutomatic .MarkerStyle = xlNone .Smooth = False .MarkerSize = 5 .Shadow = False End With ActiveChart.Axes(xlCategory).Select With Selection.TickLabels .Alignment = xlCenter .Offset = 100 .ReadingOrder = xlContext .Orientation = xlUpward End With ActiveChart.Axes(xlValue).Select With ActiveChart.Axes(xlValue) .DisplayUnit = xlNone End With ActiveChart.Axes(xlValue).MajorGridlines.Select ActiveChart.PlotArea.Select With Selection.Border .ColorIndex = 16 .Weight = xlThin .LineStyle = xlContinuous End With With Selection.Interior .ColorIndex = 2 .PatternColorIndex = 1 .Pattern = xlSolid End With ActiveChart.SeriesCollection(2).Select With Selection.Border .ColorIndex = 3 .Weight = xlThin .LineStyle = xlContinuous End With With Selection .MarkerBackgroundColorIndex = xlNone .MarkerForegroundColorIndex = xlNone .MarkerStyle = xlNone .Smooth = False .MarkerSize = 3 .Shadow = False End With ActiveChart.Axes(xlValue, xlSecondary).Select ActiveChart.Axes(xlValue).Select With Selection.Border .Weight = xlHairline .LineStyle = xlAutomatic End With With Selection .MajorTickMark = xlNone .MinorTickMark = xlNone .TickLabelPosition = xlNone End With ActiveChart.Axes(xlValue).MajorGridlines.Select With Selection.Border .ColorIndex = 57 .Weight = xlHairline .LineStyle = xlDot End With ActiveChart.Axes(xlValue, xlSecondary).Select ActiveChart.ChartArea.Select ActiveChart.Deselect End Sub Here is the worksheet part of the macro below: Dim shArr As Variant Dim shName As String 'This part clears the data worksheet Cells.Select Selection.ClearContents Range("A1").Select 'This part gets file from data directory MyDatafile = Application.GetOpenFilename("Text Files,*.Txt") With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & MyDatafile, _ Destination:=Range("A1")) .Name = "MyDataFile & Activesheet.QueryTable.counts +1" .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, 9) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With shArr = Split(MyDatafile, "\") shName = shArr(UBound(shArr)) If InStr(shName, ".") Then _ shName = Left(shName, InStr(shName, ".") - 1) ActiveSheet.Name = shName |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A reference problem - I THINK? | Excel Discussion (Misc queries) | |||
Reference problem | Excel Discussion (Misc queries) | |||
Problem with =sum(offset(cell reference,w,x,y,z). I want cell reference to be variable | Excel Worksheet Functions | |||
Complex Reference Problem | Excel Programming | |||
VBA VLookup Reference Problem | Excel Programming |