Excel VBA Connection String / Shell

[jgspencer]

I have worked with vba for the last year or so and understand a little. For the last two months I have been working on a scheduling template for my restaurants. Currently, it is working great. Most stores are now asking for the ability to be able to compare actual hours worked with scheduled hours either from up to the date aspect or weekly aspect after the week has finished. I can set a connection string to to file I need but this won't work as the dates change with each week. We are using an Aloha POS that creates grind files for each completed day. My question is:

I need to be able to create a macro that will be initiated by the user pressing a button that will start with a preset Sunday's date and then count backwards for each day stopping on Monday to pull the needed file for each day, convert the dbf to .xls and then paste the file into an excel worksheet. Each day will be posted to a separate sheet for the corresponding day of the week. From here, I can do the rest to set formulas to compare the data. I have worked a lot with vba but never attempted to do this so I don't have any current code as I don't know where to start. The code needs to use Sunday's date as the starting point so each week it pulls the correct data from the correct day. Also, if they are checking before the week or grind files have completed, I need to not error out if the day does not exist yet. The path will be C:\Aloha\dated subdirectory(format is "20090401"\GNDTIME.dbf.

Any help will be greatly appreciated. If I have not been specific or if you need more info please let me know.

 

[Andrew Poulsom]

Welcome to the Board.

To get started record a macro while using Data|Get External Data to query one of your dbf files. That will give you a connection string that you can modify in a loop.

 

[jgspencer]

Here is the code I got when I recorded the macro. I have not worked much with loops. So from here, how would I tell it to start with a Sunday's date and then count backwards to Monday and not error out if the day does not exist yet? Thanks for your help.


Sub Macro1()
'
' Macro1 Macro
'
'
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=\\192.168.108.100\bootdrv\Aloha\20090329\;Mode=Share Den" _
, _
"y Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:" _
, _
"Engine Type=18;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OL" _
, _
"EDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale" _
, _
" on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False" _
), Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdTable
.CommandText = Array("GNDTIME")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "\\192.168.108.100\bootdrv\Aloha\20090329\GNDTIME.DBF"
.ListObject.DisplayName = "Table_GNDTIME"
.Refresh BackgroundQuery:=False
End With
End Sub

 

[Andrew Poulsom]

Here is some code that starts at the previous Sunday and loops through the previous week:

Sub Test()
    Dim StartDate As Date
    Dim StrDate As String
    Dim i As Integer
    StartDate = Date - Weekday(Date) + 1
    For i = 0 To 6
        StrDate = Format(StartDate - i, "yyyymmdd")
        MsgBox StrDate
    Next i
End Sub

You would need to use concatenation to get StrDate into your query, eg:

     With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=\\192.168.108.100\bootdrv\Aloha\" & StrDate & "\;Mode=Share Den" _
        , _
        "y Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:" _
        , _
        "Engine Type=18;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OL" _
        , _
        "EDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale" _
        , _
        " on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False" _
        ), Destination:=Range("$A$1")).QueryTable

and:

        .SourceDataFile = "\\192.168.108.100\bootdrv\Aloha\" & StrDate & "\GNDTIME.DBF"

There is a function here to check if a file exists:

http://spreadsheetpage.com/index.php/tip/some_useful_vba_functions/

 

[jgspencer]

I really appreciate your help. I'll try this and see if i can't get it working. Again, Thank you.

 

[jgspencer]

I can't seem to get this to work so I'm going to try a different route. How would I start from the current date and have it perform the code and stop after Monday has completed.

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim StartDate As Date
    Dim StrDate As String
    Dim i As Integer
    StartDate = Date
    Do
        StrDate = Format(StartDate - i, "yyyymmdd")
        MsgBox StrDate
        StartDate = StartDate - 1
        If Weekday(StartDate) = vbSunday Then Exit Do
    Loop
End Sub

 

[jgspencer]

Andrew, thanks for all your help. Three more problems I'm running in to:

1. How do I get each day to copy the data into a different sheet but same range for each sheet?
2. Managers will at times adjust employees clock-in or clock-out. When I try to run the connection string, it errors out because it can't paste on top of an existing table. I'm not sure if there is another way to set a path to the data I need without using Excel's connection string. I couldn't find any options to paste as data or text, only a table or pivot table
3. The date works great, but let's say it's the following week after the schedule has completed and the managers want to see how last week looked for actual hours worked compared to scheduled hours. I guess I need the start date to always equal the sunday weekending date that they set for each schedule. For example purposes, the date will be on Sheet 1 Cell A1. How do I get the start date to equal that Sunday's date and then end when Monday has completed. In my thinking, this should still give them the ability to check the prior week (Sunday - Monday) in the current week.

Again, thanks for all your help and I hope I'm giving you everything you need to help me.

 

[Andrew Poulsom]

This code will add a worksheet for each day of the previous week, starting with Sunday:

Sub Test()
    Dim StartDate As Date
    Dim StrDate As String
    Dim i As Integer
    Dim ShNew As Worksheet
    StartDate = Date - Weekday(Date) + 1
    For i = 0 To 6
        StrDate = Format(StartDate - i, "yyyymmdd")
        Set ShNew = Worksheets.Add
        With ShNew
            .Name = StrDate
            MsgBox .Name
        End With
    Next i
End Sub

Put your query code inside the with construct.