Array loses value when a vba opened workbook it was read from closes

Question

I want to use some data stored in a VBA defined array which I read in from another workbook. I process the data in it's original workbook to remove all spaces, read the data in, and close the original workbook without saving. Then when I call another sub I find the array, and range variables I extracted from that workbook are no longer defined. Is it possible to make the values stick without keeping the workbook open? The problem is near the end of the routine, and noted in caps.
THANKS!

All subroutines that are called work properly except the last one, which is still being defined. I will share the other routines if needed or someone thinks it will solve a problem for them --DONE. There is no error checking in any of them as of yet!



    Sub UpdateEmployeewithholding()
'This sub will clean employee withholding as it is exported from QuickBooks and then read the file into this workbook
'The path is already stored in the names manager
'This routine needs to integrate the existing subs "changevalueofname" and "getpath".  They should update before executing the balance of this routine
Dim MyWorkBook As Workbook
Dim MyPath As Variant   'Contains path to employee withholding spreadsheet as exported from quickbooks.  This sheet is to be modified for reading, but not saved
Dim MyRange As Range    'Contains a defined range after setting it so
Dim whichrow As Variant 'Marks the starting point for routines that find and delete blanks as well as those that define range values and scan them into an array
Dim Direction As Variant    'Defines whether we are progressing over "Rows" or "Columns"
Dim ArrayWidth As Range  'Holds the top row addresses of the array
Dim ArrayHeight As Range 'Holds the left column addresses of the array
Dim MyArray As Variant   'Holds the array to transfer to this spreadsheet

whichrow = 1        'We are starting in cell A1 or R1C1
Direction = "Rows"


'******************************************************************************************************
'***INSERT Code that will read the string value stored in the name manager Name "PathToEmployeeWithholding" into the variable "MyPath"
' and eliminate the hard coded path from the routine
'  STILL MISSING
'*****************************************************************************************************

'Setting MyPath to the fixed path to employee withholding until we can get the routine to open the workbook from a varialbe
'stored  in the name manager
MyPath = "D:\redacted\Employee Withholding .xlsx"
'ActiveWorkbook.Names (PathToEmployeeWithholding)
Debug.Print MyPath

Set MyWorkBook = Workbooks.Open(MyPath)
Debug.Print ActiveWorkbook.Name

With MyWorkBook
    .Activate
    Call FindDataRange(MyRange, whichrow, Direction)
    Debug.Print MyRange.Address
    Call DeleteBlanks(MyRange, Direction)
    'Use ArrayWidth and ArrayHeight with the routine FindDataRange
    'to get the width and height of the final arrray that will be read into the spreadsheet
    'close without saving the data, so it will be preserved as it came from quickbooks
    Call FindDataRange(ArrayWidth, whichrow, Direction)
    Direction = "Columns)"
    Call FindDataRange(ArrayHeight, whichrow, Direction)
    Debug.Print "Array Width " & ArrayWidth.Address
    Debug.Print "array height " & ArrayHeight.Address
     'Insert a call to a routine that will copy an array consisting of myrange as the top plus all the rows under it, which include employee info
    Call ReadArray(MyArray, ArrayWidth, ArrayHeight)
    'Insert code to test employee sheets and recap sheet, as well as sheet containing lookup data.
    'As that code determines what the current structure is, it should update the structure to conform to the imported array
    'If no data exists in the spreadsheet, then we create pages for each new employee, and write the Recap and the Lookup Table
    'If data already exists in the spreadsheet, then we maintain the existing employees. and append their sheets,
    'and add new Data to the lookup table, (Questionable whether we should totally rewrite the lookup table or just append
    'the new data and sort by employee name to maintain old employee data)
    'and rewrite the Recap so the user only has to enter time for current employees
    'NOTE the employee sheets will be labeled by their name, just as listed in the lookup table
    .Close (False)
    

End With

ResetMessage = MsgBox("You are about to reset the spreadsheet to match the data that was just loaded.  Continue?", vbOKCancel)
If ResetMessage = 2 Then
Exit Sub
End If

Call ResetWorksheets(MyArray, ArrayWidth, ArrayHeight)  '**ALL OF THESE VARIBALES LOSE VALUE WHEN MY WORKBOOK CLOSES**



End Sub


Sub FindDataRange(MyRange As Range, whichrow As Variant, Direction As Variant)
'This routine will return a single row or Column range of data
'that includes the first cell in whichrow to the last cell with data in whichrow



Dim StartRange As Range
Dim FullRange As Range

If Direction = "Rows" Then
'Startrange will be first cell in whichrow
Set StartRange = Cells(whichrow, 1)

'Fullrange will be the entire row of whichrow
Set FullRange = Range(StartRange, StartRange.Offset(0, Columns.Count - 1))    'this produced the entire whichrow row as the range.
Set MyRange = Range(StartRange, FullRange.Find("*", StartRange, xlValues, xlPart, xlByRows, xlPrevious, True))       'startrange,xlvalues,xlpart,xlbyrows,xlPrevious,true)
'this should return the range which has the data
Debug.Print MyRange.Address
Else
'Startrange will be first cell in whichrow
Set StartRange = Cells(1, whichrow)
'Fullrange will be the entire column of whichrow
Set FullRange = Range(StartRange, StartRange.Offset(Rows.Count - 1, 0))    'this produced the entire whichrow column as the range.
Set MyRange = Range(StartRange, FullRange.Find("*", StartRange, xlValues, xlPart, xlByColumns, xlPrevious, True))       'startrange,xlvalues,xlpart,xlbyrows,xlPrevious,true)
'this should return the range which has the data
Debug.Print MyRange.Address
End If



End Sub




Sub DeleteBlanks(WorkingRange As Range, Direction As Variant)
'This will delete the entire row/column of blanks according to the cell in a single row/column contents
'To use it we need to input a working range that is to be considered to delete blanks from, and a direction which is either "Rows" or "Columns"
Dim Message As String
For i = WorkingRange.Cells.Count To 1 Step -1
Debug.Print Direction
    
    Select Case Direction
    
    Case Is = "Rows"
             If WorkingRange.Cells(i) = "" Then
                Debug.Print WorkingRange.Cells(i).Address
                WorkingRange.Cells(i).EntireColumn.Delete
                End If
    Case Is = "Columns"
                    If WorkingRange.Cells(i) = "" Then
                    WorkingRange.Cells(i).EntireRow.Delete
                    Debug.Print WorkingRange.Cells(i).Address
                    End If
    Case Else
                    Message = "You must declare a direction either Rows or Columns to search before calling this routine"
                    MsgBox Message, vbOKOnly, "Routine Requires a Direction"
                
            
    
    
    End Select



Next

End Sub


Sub ReadArray(MyArray As Variant, ArrayWidth As Range, ArrayHeight As Range)
'This routine should read an array with a width contained in the ArrayWidth range, and a height
'contained in the ArrayHeight range. We retreive the actual size to read by using range.cells.count
Dim WidthStep As Long    'Contains the width of the array
Dim HeightStep As Long   'Contains the height of the array
Dim i As Long            'step counter for height becaue it has to be the outside loop to read in rows
Dim j As Long            'step counter for width because it has to be the inside loop to read in rows



WidthStep = ArrayWidth.Cells.Count
HeightStep = ArrayHeight.Cells.Count


ReDim MyArray(HeightStep, WidthStep)
' Let's read the array in in rows, but remember the employee names are in the left column

For i = 1 To HeightStep

    For j = 1 To WidthStep
    MyArray(i, j) = ArrayWidth.Cells(i, j).Value
    Debug.Print MyArray(i, j)
    Next j

'!!!!!!!!This routine READS LEFT TO RIGHT FIRST AND THEN TOP TO BOTTOM
'Writing must consider how it is reading to get things in the correct place

Next i



End Sub

Sub ResetWorksheets(MyArray As Variant, ArrayWidth As Range, ArrayHeight As Range)
'Currently a blank subroutine with a test to verify data transfered
For i = 1 To ArrayHeight.Cells.Count
Debug.Print MyArray(1, i).Value

Next i

End Sub

Array loses value when a vba opened workbook it was read from closes

Answers (1)

Related Questions