Different ActiveWorkbook and Different ActiveSheet

[QuestionBaker]

I am writing a macro which is editing data in a different excel file.

Let,
Excel_with_Macro be the excel file where I am writing the macro
Sheet1_EwM be the sheet of excel file Excel_with_Macro
Excel_2b_edited be the excel whose data I am editing
data_E2bE be the sheet of Excel_2b_Edited and contains the data in it

I have been able to successfully perform most of my calculations but am facing problem with plotting charts, the chart is being plotted in Sheet1_EwM and without any data

I did some investigation and have found the source of the problem, but haven't been able to rectify it the problem is as follows
ActiveWorkbook.Name returns me Excel_2b_Edited
ActiveSheet.Name returns me Sheet1_EwM


In the very beginning, I use the following command to open and activate data_E2bE
Workbooks.Open FileName:=(D:\ExcelFiles\Excel_2b_edited.xlsx)
Workbooks(Excel_2b_edited.xlsx).Worksheets(data_E2bE).Activate

By using the above two lines of code, I am able to delete rows, copy information from specific cells, paste results in specific cells but not plot a chart with it


I am using the following command to plot the chart
ActiveSheet.Shapes.AddChart2(XlChartType:=xlXYScatterLinesNoMarkers).Select

Does anyone know how to resolve this issue?

 

[Michael M]

I don't have Excel at the moment, but it would be far easier to simply post ALL of the code, and tell us what and where the problem lies....not a breakdown of what you think the problem is.

 

[QuestionBaker]

Here is my entire code, if it is of any help almost each line is commented
The lines that I've mentioned are at the very beginning (line 37 and 38) and at the very end line (154, 157(checking what are my active workbooks and worksheets), 160[plot command commented])

Sub FEMacro()
Application.ScreenUpdating = True       'If false, then stops screen updating (aviods screen flicker, if any)


    'Variable declaration  (It seems it is not a necessary step, as I am able to get away without defining variable types, and directly using them as needed)
Dim FilePath As String                  'File path where the current file is saved
Dim NewFileName As String               'Name of newly saved file (It is the file with data analysis)
Dim FileName As String                  'Name of current file
Dim File4Ana As String                  'Address and filename of the other excel file whose analysis is to be performed


TimeCycle = 1180                        'Standard Cycle time for NEDC90 (units s)
IdlePost = 22                           'Idling time at the very end of the cycle
IdlePre = 11                            'Idling time at the very start of the cycle




    'Default headings as obtained form CAN signal, they will be used to identify in which column data is store
SheetAna = "s201 g175 warm up behaviour tri"    'Sheet name where the data is present, checking previous data, this sheet name seems to be constant throught tests
TextOdo = "ODO_DISTANCE_EMS"            'Default heading for odo column
TextSpe = "VEHICLE_SPEED_EMS"           'Default heading for vehicle speed column
TextCon = "TotalConsumption"            'Default heading for total fuel consumption column
TNR = "Times New Roman"                 'Font
Black = RGB(0, 0, 0)                    'Colour for font
XaxTime = "Time (s)"                    'X axis title for time
YaxFuel = "Fuel Consumption (l)"        'Y axis title for Fuel Consumption Chart
YaxSped = "Speed (km/h)"                'Y axis title for Speed Chart
SpedvTi = "Speed vs Time  (NEDC 90)"       'Chart Title for Speed vs time




    'Saves file names and path for file handling (opening and closing files)
FilePath = Application.ActiveWorkbook.Path      'File path where the current file is saved
FileName = Application.ActiveWorkbook.Name      'Name of this workbook (the one with macro written in it, may be redundant)
FileNameAna = Dir(FilePath & "" & "*.xlsx")    'Identifies name of the second
File4Ana = (FilePath & "" & FileNameAna)       'Saves full file path of file to be analysied


Range("A1") = File4Ana


    'Opens file and switches focus to the corret sheet
Workbooks.Open FileName:=(File4Ana)                         'Opens the file used for analysis
Workbooks(FileNameAna).Worksheets(SheetAna).Activate        'Switches focus to the excel sheet in excel file where Analysis is to be conducted


    'Checks if the last row has data points for all columns, if not then it delets the last row
    'Last row may not have data points for all columns because data logging could have stopped before their data was logged
Range("A1").Select                  'Selects first heading cell
ActiveCell.Offset(3, 0).Select      'Moves down 3 spaces (based on how the excel file is generated, data starts from 3 cells below the heading)
Selection.End(xlDown).Select        'Moves down to the last cell and selects it
FCLR = ActiveCell.Address           'Gets cell address of first column last row
Range("A1").Select                  'Selects first heading cell
Selection.End(xlToRight).Select     'Executes Ctrl + --> and moves to last heading, (assumes that there are no blank columns in between)
LastHead = ActiveCell.Address       'Gets cell address of last heading
'Performs the check here, if data is not uniformly present, it delets the last row
'This would help later when we plot various graphs, we wouldn't have to deal with mismatch in x and y data points
If IsEmpty(Cells(Range(FCLR).Row, Range(LastHead).Column)) = True Then
    Rows(Range(FCLR).Row).Select
    Selection.ClearContents
End If


    'Finds Odo data, and stores address of first and last cell of the data column
Set HeadOdo = Range("A1", "CC1").Find(TextOdo, Lookat:=1)     'Searches for TextOdo in cells defined in Range, Lookat:=1 tells to perform a complete search and not to return value if a partial match is found.  This is important otherwise, we would get seconds data and not the actual data
Range(HeadOdo.Address).Select       'Selects Heading Cell containing Odo data
ActiveCell.Offset(3, 0).Select      'Moves down 3 spaces (based on how the excel file is generated, data starts from 3 cells below the heading)
OdoStart = ActiveCell.Address       'Cell from where Odo readings start (units m)
Range(OdoStart).Select              'Selects OdoStart as the active cell (used to identify last cell in odo column)
Selection.End(xlDown).Select        'Moves down to the last cell and selects it
OdoEnd = ActiveCell.Address         '(ReferenceStyle:=xlR1C1)         'Stores the address of last cell of odo column
    
    'Finds fuel consumption data, and stores address of first and last cell of the data column
Set HeadCon = Range("A1", "CC1").Find(TextCon, Lookat:=1)     'Searches for TextCon in cells defined in Range, Lookat:=1 tells to perform a complete search and not to return value if a partial match is found.  This is important otherwise, we would get seconds data and not the actual data
Range(HeadCon.Address).Select       'Selects Heading Cell containing fuel consumption data
ActiveCell.Offset(3, 0).Select      'Moves down 3 spaces (based on how the excel file is generated, data starts from 3 cells below the heading)
FuelStart = ActiveCell.Address      'Cell from where fuel consumption readings start (units l)
Range(FuelStart).Select             'Selects FuelStart as the active cell (used to identify last cell in fuel consumption column)
Selection.End(xlDown).Select        'Moves down to the last cell and selects it
FuelEnd = ActiveCell.Address        '(ReferenceStyle:=xlR1C1)        'Stores the address of last cell of fuel consumption column


    'Finds vehicle speed data, and stores cells address of first and last non zero speeds in the cycle
Set HeadSpe = Range("A1", "CC1").Find(TextSpe, Lookat:=1)     'Searches for TextCon in cells defined in Range, Lookat:=1 tells to perform a complete search and not to return value if a partial match is found.  This is important otherwise, we would get seconds data and not the actual data
Range(HeadSpe.Address).Select       'Selects Heading Cell containing vehicle speed data
ActiveCell.Offset(3, 0).Select      'Moves down 3 spaces (based on how the excel file is generated, data starts from 3 cells below the heading)
MoveStart = ActiveCell.Address      'Cell from where fuel consumption readings start (units l)
'Keeps going one cell down at a time till it encounters a non zero cell
Do While Range(MoveStart) = 0
    ActiveCell.Offset(1, 0).Select
    MoveStart = ActiveCell.Address
Loop
Selection.End(xlDown).Select        'Moves down to the last cell and selects it
MoveEnd = ActiveCell.Address
'Keeps going one cell up at a time till it encounters a non zero cell
Do While Range(MoveEnd) = 0
    ActiveCell.Offset(-1, 0).Select
    MoveEnd = ActiveCell.Address
Loop


    'Calculates time period Vehicle Movement Start to Vehicle Movement End (THIS  CALCULATION  SHOULD  BE  DONE  ON  TIME  COLUMN  WHICH  SHOULD  BE  PROPERLY  IDENTIFIED  AND  NOT  HARD  CODED)
Range(MoveStart).Select                         'Selects MoveStart cell
ActiveCell.Offset(0, -1).Select                 'Moves one column left, on to time column
MoveStartTime = ActiveCell.Address              'Stores address of time cell when movement starts


Range(MoveEnd).Select                           'Selects MoveStart cell
ActiveCell.Offset(0, -1).Select                 'Moves one column left, on to time column
MoveEndTime = ActiveCell.Address                'Stores address of time cell when movement starts


TimeMovement = Range(MoveEndTime) - Range(MoveStartTime)    'Movement time period




        'FEs Calculation (FE is calculated by different methods)
    'FE for the complete test
DistCoveredTotal = Range(OdoEnd) - Range(OdoStart)      'Total distance covered by the vehcile (m)
FuelConsumedTotal = Range(FuelEnd) - Range(FuelStart)   'Total fuel consumed by the vehicle (l)
FETotal = DistCoveredTotal / (1000 * FuelConsumedTotal) 'FE based on above values
Range("I2") = FETotal
    
    'FE for EUDC (Initial idling is not considered in EUDC Cycle)
'Identifies point where EUDC Starts and ends
Range(MoveEnd).Select                   'Selects cell when vehicle stops
ActiveCell.Offset(-3, 0).Select         'Selects 3 cell above when the vehicle stops, (we will now move up till we encouter 0 speed)
MoveEUDCStart = ActiveCell.Address      'Sets MoveEUDCStart as the current selected cell
Do While Range(MoveEUDCStart) > 0
    ActiveCell.Offset(-1, 0).Select
    MoveEUDCStart = ActiveCell.Address
Loop
'Determinates where EUDC should end (ideally after IdlePost number of cells after vehicle stops, but if data is not present for that region, it just selects the last cell)
If IsEmpty(Cells(Range(MoveEnd).Row + IdlePost, Range(MoveEnd).Column)) = True Then
    MoveEUDCEnd = Cells(Range(OdoEnd).Row, Range(MoveEnd).Column).Address
Else
    MoveEUDCEnd = Cells(Range(MoveEnd).Row + IdlePost, Range(MoveEnd).Column).Address
End If
DistCoveredEUDC = Cells(Range(MoveEUDCEnd).Row, Range(OdoStart).Column) - _
                  Cells(Range(MoveEUDCStart).Row, Range(OdoStart).Column)               'Calculates total distance covered in EUDC phase of NEDC
FuelConsumptionEUDC = Cells(Range(MoveEUDCEnd).Row, Range(FuelStart).Column) - _
                  Cells(Range(MoveEUDCStart).Row, Range(FuelStart).Column)              'Calculates total fuel consumed in EUDC phase of NEDC
FEEUDC = DistCoveredEUDC / (1000 * FuelConsumptionEUDC)         'FE in the EUDC cycle
Range("L2") = FEEUDC
    
    'FE for UDC
MoveUDCEnd = MoveEUDCStart              '(UDC ends when EUDC begins)
'Determines when UDC should start (it looks up IdlePre number of Cells above the first time vehicle started moving,
'if it finds data, them UDC starts from that point, if not then it assumes that cycle started from when the data logging started)
If (Range(MoveStart).Row - IdlePre) < 4 Then
    MoveUDCStart = Cells(4, Range(MoveStart).Column).Address
Else
    MoveUDCStart = Cells(Range(MoveStart).Row - IdlePre, Range(MoveStart).Column).Address
End If
DistCoveredUDC = Cells(Range(MoveUDCEnd).Row, Range(OdoStart).Column) - _
                  Cells(Range(MoveUDCStart).Row, Range(OdoStart).Column)                'Calculates total distance covered in EUDC phase of NEDC
FuelConsumptionUDC = Cells(Range(MoveUDCEnd).Row, Range(FuelStart).Column) - _
                  Cells(Range(MoveUDCStart).Row, Range(FuelStart).Column)               'Calculates total fuel consumed in EUDC phase of NEDC
FEUDC = DistCoveredUDC / (1000 * FuelConsumptionUDC)         'FE in the UDC cycle
Range("O2") = FEUDC
    
    
        'Plotting all the relevant graphs
    'Plotting speed vs time
Dim data As Worksheet
Set data = ActiveSheet
Range("C2") = ActiveSheet.Name
'Worksheets(SheetAna).Activate
Range("E2") = data.Name
Range("D2") = ActiveWorkbook.Name
''''''Range(Range(MoveUDCStart).Address, Range(MoveEUDCEnd).Offset(0, -1).Address).Select     'Selects range of cells from begining of UDC to end of EUDC
''''''Workbooks(FileNameAna).Worksheets(SheetAna).Activate
''''''ActiveSheet.Shapes.AddChart2(XlChartType:=xlXYScatterLinesNoMarkers).Select      'Creates a chart of XY Scatter Lines No Markers type
''''''Set SpeedvsTime = ActiveChart                   'Gives a name to the selected chart
''''''With SpeedvsTime
''''''    With .ChartTitle                            'Selects chart title for formatting
''''''        .Text = SpedvTi                         'Sets title text
''''''        .Font.Name = TNR                        'Sets title font
''''''        .Font.Color = Black                     'Sets font colour as black
''''''    End With
''''''    With .Axes(xlCategory, xlPrimary)           'X axis
''''''        .HasTitle = True                        'Enables X axis
''''''        With .AxisTitle
''''''            .Text = XaxTime                     'Sets X axis title
''''''            .Font.Name = TNR                    'Sets font
''''''            .Font.Size = 12                     'Sets font size
''''''            .Font.Color = Black                 'Sets font colour
''''''        End With
''''''    End With
''''''    With .Axes(xlValue, xlPrimary)              'Y axis
''''''        .HasTitle = True                        'Enables Y axis
''''''        With .AxisTitle
''''''            .Text = YaxFuel                     'Sets Y axis title
''''''            .Font.Name = TNR                    'Sets font
''''''            .Font.Size = 12                     'Sets font size
''''''            .Font.Color = Black                 'Sets font colour
''''''        End With
''''''    End With
''''''    .Parent.Height = 325
''''''    .Parent.Width = 500
''''''    .Parent.Top = 100
''''''    .Parent.Left = 100
''''''End With
    
    
End Sub

 

[Michael M]

Check this line

FileNameAna = Dir(FilePath & "" & "*.xlsx")

I think should be

FileNameAna = Dir(FilePath & "\" & ".xlsx")

I note also that you have not declared a lot of your variables, which leaves you open to getting the syntax of said variable wrong.

 

[QuestionBaker]

You are right, it should be

FileNameAna = Dir(FilePath & "\" & ".xlsx")

and not

FileNameAna = Dir(FilePath & "" & "*.xlsx")

and it is so, I think I may have messed up copy pasting in some way, or the "/" may have got deleted somehow. But my code does contain forward slash and the problem still persists with it.

 

[Norie]

Instead of relying on ActiveWorkbook/ActiveSheet etc. and activating/selecting you should create references to the workbooks/sheets you are working with and use them in the code.

I don't have time to go through all the code but you could start by creating references to the analysis workbook/worksheet like this.

Dim wbAnalysis As Workbook
Dim wsAnalysis As Worksheet

' other code

    ' open file used for analysis and create a reference to it
    Set wbAnalysis =  Workbooks.Open(FileName:=File4Ana)
    
    ' set a reference to the sheet where Analysis is to be conducted
    Set wsAnalysis = wbAnalysis(SheetAna)       'Switches focus to the

If you had the above code you could then use something like this to get the values for the variables FCLR and LastHead

With wsAnalysis
    FCLR = .Range("A4").End(xlDown).Address
    LastHead = .Range("A1").End(xlToRight).Address
End With

 

[QuestionBaker]

Sorry for the late reply, I was unable to work on my code for a few days.

Norie, it seems your suggestion worked

What I've noticed as the source and solution to my problem laid in how the excel file is opened

My original code used (line 37)

Workbooks.Open FileName:=(File4Ana)

And now I've adopted to V and it seems to work without a hitch

Set wbAnalysis = Workbooks.Open(FileName:=File4Ana)     ' open file used for analysis and create a reference to it
    Set wsAnalysis = wbAnalysis.Sheets(SheetAna)            ' set a reference to the sheet where Analysis is to be conducted
    
    Workbooks(FileNameAna).Sheets(SheetAna).Activate
    With wsAnalysis
        .Range("A4", Range("B5").End(xlDown).Address).Select
        .Shapes.AddChart2(XlChartType:=xlXYScatterLinesNoMarkers).Select      'Creates a chart of XY Scatter Lines No Markers type
    End With

The following code also works without any probem

Set wbAnalysis = Workbooks.Open(FileName:=File4Ana)     ' open file used for analysis and create a reference to it
    Set wsAnalysis = wbAnalysis.Sheets(SheetAna)            ' set a reference to the sheet where Analysis is to be conducted
    
    Workbooks(FileNameAna).Sheets(SheetAna).Activate
    'With wsAnalysis
        Range("A4", Range("B5").End(xlDown).Address).Select
        ActiveSheet.Shapes.AddChart2(XlChartType:=xlXYScatterLinesNoMarkers).Select      'Creates a chart of XY Scatter Lines No Markers type
    'End With

 

[QuestionBaker]

New Finding!

The problem persisted, until the following was done

The above correct code was written in Module1, whereas my original code was written in ThisWorkbook.

I moved my code from ThisWorkbook to Module1 (along with the correction in file opening) to Module1 and it seems to be functioning properly.

Does anyone know why this problem occurs in ThisWorkbook and not in Module1

Now that I think about it, my original code may also work as it were written, but I haven't checked it yet.

 

[Norie]

It could be that any code in the ThisWorkbook module was referring to ranges/sheets etc. in the workbook the code was in, i.e. ThisWorkbook.