Chart Tab Name & Worksheet Reference
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 |
Chart Tab Name & Worksheet Reference
Dim shName at the top of the code module, before any macros. This will
retain the variable for use in any macro within this code module. Now you can substitute that variable into line 4 of the chart code as long as shName obtained a value before the chart code is run. if you Dim a variable within the macro, it will lose it's value when the macro ends. Read VB Help on the 'life of a variable' for more tips. Mike F "bob" wrote in message ... 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 |
Chart Tab Name & Worksheet Reference
So after I declare shName, I should replace "AAA" with shName? Is that all
that there is to it? Thanks "Mike Fogleman" wrote in message news:I7cTc.302638$XM6.51751@attbi_s53... Dim shName at the top of the code module, before any macros. This will retain the variable for use in any macro within this code module. Now you can substitute that variable into line 4 of the chart code as long as shName obtained a value before the chart code is run. if you Dim a variable within the macro, it will lose it's value when the macro ends. Read VB Help on the 'life of a variable' for more tips. Mike F "bob" wrote in message ... 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 |
Chart Tab Name & Worksheet Reference
Mike: I just checked, and I already have Dim shName at the beginning of the
macro. If you scroll down through the oriignal message a bit you'll see I'm using shName for getting the data intot the worksheet. Thanks "Mike Fogleman" wrote in message news:I7cTc.302638$XM6.51751@attbi_s53... Dim shName at the top of the code module, before any macros. This will retain the variable for use in any macro within this code module. Now you can substitute that variable into line 4 of the chart code as long as shName obtained a value before the chart code is run. if you Dim a variable within the macro, it will lose it's value when the macro ends. Read VB Help on the 'life of a variable' for more tips. Mike F "bob" wrote in message ... 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 |
All times are GMT +1. The time now is 04:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com