none
Changing Data Fetching In Established VBA Project RRS feed

  • Question

  • Quick background. I'm a SOLO IT Admin/Director for small business and my strength is administration, workstation, server support, virtualization and "gadgets." They want me to finally turn some much needed attention to our plethora of Excel spreadsheets that contain a lot of VBA, many of which has been in use for 10+ years. I'm game, but got some study ahead of me.

    I've been tasked to change a way a sheet grabs data. Sheet looks like this:

    I added the column W when I started to work on this project and will explain below...

    Basically this sheet grabs data (job numbers) from another sheet according to a date range and calculates them with other subroutines and places those job numbers in the cells starting at C2. For the sake of this post, no need to worry about all those other cells because if I can just get this started, the rest will fall into place.

    We want to eliminate the need to look at that other sheet based upon date range to get the job numbers and just have the macro (RUN REPORT, top left) grab the "jobs" listed in column W that the user will manually enter.

    There are the two code blocks that are used to grab this data...

    Sub IMPORT_ALL_INFORMATION()
    
    'Set variables
    Dim file_in As Long
    Dim strInput As Variant
    Dim i As Integer
    Dim j As Integer
    Dim l As Integer
    Dim sTmp As String
    Dim sJob As String
    Dim sSchedPath As String
    
    'end setting variables
    Sheets("REPORT").Select
    Range("C2").Select
    
    sSchedPath = "C:\Temp"
    Call apiCopyFile("\servername\Applications\Schedule\schedule-s\schedule, S.xls", "C:\Temp\schedule, S.xls", 0)
    dteStart = Application.Sheets("Report").Range("$G$27").Value
    dteEnd = Application.Sheets("Report").Range("$J$27").Value
    l = 4 ' First data row of schedule, S.xls
    j = 2 ' First job row of Plate & Bar Spreadsheet
    Do Until CDate(GetDate(GetValue(sSchedPath, "schedule, S.xls", "LOG (2)", "N" & CStr(l)))) >= dteStart
    l = l + 1
    ' Changed from 754 to 854...may be total jobs for year...went close to 800 jobs this year . BAW
    If l = 854 Then
    MsgBox ("Hello")
    End If
    sTmp = Trim$(GetValue(sSchedPath, "schedule, S.xls", "LOG (2)", "N" & CStr(l)))
    If sTmp = "0" Or Len(sTmp) = 0 Or sTmp = "HOLIDAY" Then
    l = l + 1
    End If
    Loop
    Do
    sJob = ParseJob(GetValue(sSchedPath, "schedule, S.xls", "LOG (2)", "B" & CStr(l)))
    ' Debug.Print sJob
    vJobFolders = Split(FindJobDir(strpathtofile & sJob), ",")
    For i = 0 To UBound(vJobFolders)
    On Error GoTo ErrorExit
    Application.Sheets("report").Range("C" & CStr(j)).Value = vJobFolders(i)
    j = j + 1
    file_in = FreeFile 'file number
    strFileToOpen = strpathtofile & vJobFolders(i) & strFilename
    If Dir(strFileToOpen) <> "" Then
    Open strFileToOpen For Input As #file_in
    Put_Data_In_Array (file_in)
    Organize_Array_For_Print
    Close #file_in ' close the file
    End If
    ErrorExit:
    Next i
    l = l + 1
    sTmp = Trim$(GetValue(sSchedPath, "schedule, S.xls", "LOG (2)", "N" & CStr(l)))
    If sTmp = "0" Or Len(sTmp) = 0 Or sTmp = "HOLIDAY" Then
    l = l + 1
    End If
    Loop Until CDate(GetDate(sTmp)) >= dteEnd
    Sheets("REPORT").Select
    
    End Sub
    
    Function GetValue(path, file, sheet, ref) As String
    ' Retrieves a value from a closed workbook
    Dim arg As String
    Dim pos As Integer
    ' Make sure the file exists
    If Right(path, 1) <> "" Then path = path & ""
    If Dir(path & file) = "" Then
    GetValue = "File Not Found"
    Exit Function
    End If
    ' Create the argument
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
    Range(ref).Range("A1").Address(, , xlR1C1)
    ' Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
    ' Strip Any time from beginning of Date string
    pos = InStr(GetValue, ":")
    If pos <> 0 Then GetValue = Mid$(GetValue, pos + 3)
    End Function

    Just don't know how to tell it, with VBA, to take each job number in column W and do what it normally did the other way and then put a warning up if it doesn't match exactly and to STOP when it teaches a null cell.

    Any suggestions on where to find some examples of this? If I can just get this part going, I am fairly confident I can do the rest. I can post the existing subroutines code if needed. I am thinking is is much easier than I think it will be. Just need the data from column W now and then it should feed into the rest of the arrays and produce the needed data.

    Thanks so much!

    Sunday, May 29, 2016 11:25 PM

All replies

  • Just looking at some of the data it looks like you should use a database to store the data rather than a lot of spreadsheets.  Workbooks are cumbersome to work with and prone to loss of data.  You can still get to the database using Excel/Word and also create web interfaces to the data.  If it is a small business you can use Access, MySQL, SQL Server or several other options.  I just hate to see you invest a lot of effort into an inadequate solution.
    Monday, May 30, 2016 2:49 AM
  • Just looking at some of the data it looks like you should use a database to store the data rather than a lot of spreadsheets.  Workbooks are cumbersome to work with and prone to loss of data.  You can still get to the database using Excel/Word and also create web interfaces to the data.  If it is a small business you can use Access, MySQL, SQL Server or several other options.  I just hate to see you invest a lot of effort into an inadequate solution.

    Agreed for sure and part of studies include SQL. But this is an established sheet that pulls in text data from a drafting program and we have a ton of projects that pull flat text data from this drafting programs.

    Back to this sheet, we just want to list the jobs we want calculated in Column W rather than going to this other sheet for now. Just not sure how to assign a variable to date in this range and then stop when it gets null. I believe it will continue to use the existing arrays...

    Monday, May 30, 2016 3:14 AM
  • As mentioned, the Database project will come later...I just need to get the data as described above. I am guessing this will only need a few lines of code but need a little help. 
    Saturday, June 11, 2016 8:43 PM