Hello honkin,
In advance and for completeness, whenever I say "copy-DBF/paste-XL" within the following, I'm referring to the code as in my post #16.
Glad it's basically working for you, now we have to polish it to make it work completely as intended, especially in connection with your additional request as from your previous post #22. Arriving at that point I would like to have a full picture of what you would like to have as an end result. Because if I summarize all your given input so far, there are still a few uncertainties that I would like to be cleared, but I could have overlooked something. Later on I will explain. In case you can confirm the following we're making progress:
1). all actions that you want to be automated (ie copy-DBF/paste-XL) can now take place entirely in a virtualized Windows environment on your Mac
2). there are multiple DBF files with a filename formatted like "SByyyymmdd.dbf" (year, month, day)
3). those files only occur once a day, each day's date is in the file name as formatted in [2)], however ...
4). ... those files are updated every two hours
5). the copy-DBF/paste-XL code works as intended, however some amendments are required due to a time difference
6). the copy-DBF/paste-XL code is currently running within your Personal Workbook (PERSONAL.XLSB) and is launched manually
7). you're about to write (quote) "
a simply VBA script file to open that file (PERSONAL.XLSB) and run the macro"
8). you're about to write a VBS script and a Windows Console batch file to run from Windows Task Scheduler in order to launch the copy-DBF/paste-XL code
9). you're in need of two macros (quote) "
one macro to open for today up until a certain time and one to do it for tomorrow's date after that time"
ad 1) & 2):
Seems clear to me, just to be sure
ad 3) & 4):
The update of the DBF file could occur in two different manners; either a replacement of all of its contents with fresh data or (the other way) just newly appended data. In case of a replacement there would be no special amendments required within my code. In case of the latter some data will almost certainly be copied several times across the Prices workbook, resulting in one ore more duplicates. If that's not a problem for you, it would be fine. If it would be a problem a radical change of the existing code is necessary.
ad 5):
please see [9)]
ad 6):
It is preferable not to use the Personal Workbook for running the copy-DBF/paste-XL, especially since you want to start everything automatically from Windows Task Scheduler.
ad 7):
No additional VBA script (macro) is required to open the Personal Workbook. Your Personal Workbook opens automatically in the background every time Excel is started. However, as said before, a separate workbook (other than the Personal Workbook) is required to run the code from, otherwise (when at the end everything is implemented to run automatically) the copy-DBF/paste-XL code will run every time you start Excel manually for other purposes, and that might be unwanted.
Actually we are in the need of two different workbooks due to your additional request. Although it's possible within Windows Task Scheduler to pas parameters on to the program that has to be scheduled (ie Excel), any necessary parameters (eg date or time information) cannot be passed that way to the workbook to be opened (and its VBA code). There are other ways to accomplish something like parameter transfer but it then gets rather involved. Moreover, it is not necessary.
ad 8):
It may be sufficient to choose either Windows Script Host (.vbs) or a batch file (either .bat or .cmd). A multiple stage process to start Excel in order to execute the copy-DBF/paste-XL code from Windows Task Scheduler seems unnecessary to me unless you have reason to do so. Since you're apparently familiar with VBscript it might be worth mentioning, that the single quotation mark in VBA code is similar to the REM statement in VBscript.
ad 9):
That can be easily implemented. In connection with this, I think that at VBA level it is wise to only take the date difference into account. You can take the time difference into account when scheduling within Windows Task Scheduler, if you agree. Please see also my notes at [7)].
For now I would recommend you to create a blank workbook. Save it as a macro enabled workbook on disk, for instance within the C:\Price folder, and type the file name: DBF_TODAY.XLSM. Now open the VBE (Alt F11 or -since you have a Mac- developer tab > visual basic). In the left hand pane double click on ThisWorkbook of DBF_TODAY.XLSM to open the ThisWorkbook module and paste the proper code (see below) in the right hand pane. Click on menu > debug > compile VBAproject and finally save the workbook again. Close this workbook and perform the same actions in the same order with a blank workbook which has to be named DBF_TOMORROW.XLSM. Be sure to paste the proper code (see below).
Since both workbooks are intended to perform a task independently and scheduled through the Windows Task Scheduler, the code starts automatically the moment those workbooks are being opened. When the task is finished, Excel will automatically close unless something unexpected goes wrong. In that case it's likely the code will display a warning on the screen, as you have experienced before (your post # 17). Excel will not be closed until the message box with the warning is closed. If you don't want this to be happening, no matter what, you may disable the three lines of code each beginning with
MsgBox using the single quote (like REM in VBscript).
Please keep in mind that the provided code assumes that the Prices.xlsx workbook has only one worksheet; this worksheet is therefore by definition the active worksheet when Prices.xlsx is opened so nothing can go wrong.
There is one note of special importance. All files that use the provided code are therefore "designed" to be opened automatically so they will also close automatically by an enforced closing of the Excel instance in which they were opened. For maintenance purposes they have to be opened in a special way. First open Excel, then click ribbon > file tab > open. If those files happen to be listed in the recent workbooks, press and hold down the left Shift key while clicking on the desired filename. If they are not listed use browse to navigate to the folder in which they are located, click on the desired file name, press and hold down the left Shift key while clicking on the Open button. The code will not be launched and Excel will not be closed automatically.
A batch file to start the copy-DBF/paste-XL task and to be scheduled in Windows Task Scheduler could look like this:
START /MIN excel.exe /e /r /x "C:\Price\dbf_today.xlsm"
So just one command line. Note the double quotation marks, not realy necessary unless there are spaces within path name or file name. Perhaps some explanation is required. The START statement launches Excel in a way you presumably want (ie as silent as possible) using the /MIN parameter followed by the name of Excels binary file to launch. The /e parameter disables Excels startup notification. The /r parameter enforces Excel to open the requested file as read-only, just to avoid any potential conflict. The /x parameter launches a separate instance of Excel. In a scenario you are using by any chance Excel at the moment one of the copy-DBF/paste-XL tasks are launched, there never would be a conflict, unless you are using the destination workbook which de DBF data is to be copied across (ie Prices.xlsx). It's therefore recommended to copy your Prices.xlsx file each time you're about to examine the imported DBF data and to use that copy for your survey.
In order to create such a batch file you may use your favorite text editor within Windows. Also Windows' notepad can be used. Quickly launch notepad by typing notepad in the search field on the windows taskbar, type (or copy/paste) the above commandline, save it (either press Ctrl Shift S or click menu > file > save as) click on the save as drop down and change it to "All files (*.*)", navigate to the folder in which you want the batch file to be stored, enter a file name (eg dbf_today.bat) and click the save button. Repeat these actions in order to create also a batch file for launching the dbf_tomorrow.xlsm file. Both batch files can be scheduled within the Windows Task Scheduler, which I assume you know how it works. If this is not the case, you can find a fairly extensive roadmap on this link.
Task Scheduler is a Windows applications which enables the user to schedule different tasks, such as enabling or disabling programs.
windowsreport.com
Hopefully everything regarding your issue has been covered. Let me know if it isn't.
This goes in the
ThisWorkbook module of
DBF_TODAY.xlsm
VBA Code:
Option Explicit
Private Sub Workbook_Open()
Call Copy_Todays_DBF_to_PricesWBK
Me.Saved = True
Application.Quit
End Sub
Private Sub Copy_Todays_DBF_to_PricesWBK()
Const cRootFolder As String = "C:\Price\" ' <<<<< change accordingly (without year!)
Const cDestWorkBk As String = "Prices.xlsx" ' <<<<< change accordingly
Dim oWsSrc As Worksheet
Dim oWsDest As Worksheet
Dim raSrc As Range
Dim raDest As Range
Dim sPath As String
Dim sDBF As String
Dim sFName As String
Dim dtDate As Date
' assign today's date
dtDate = Date
' compose path for today's year
sPath = cRootFolder & Year(dtDate) & "\"
' compose file name
sDBF = "SB" & Year(dtDate) & IIf(Len(Month(dtDate)) = 1, "0" & Month(dtDate), Month(dtDate)) & _
IIf(Len(Day(dtDate)) = 1, "0" & Day(dtDate), Day(dtDate)) & ".dbf"
' check within folder on existence of file
sFName = Dir(sPath & sDBF)
If Len(sFName) > 0 Then
' change some settings for the benefit of speed
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
' open DBF file
On Error Resume Next
Set oWsSrc = Workbooks.Open(sPath & sFName).ActiveSheet
If oWsSrc Is Nothing Then GoTo ERROR_DBF
' open destination workbook
Set oWsDest = Workbooks.Open(sPath & cDestWorkBk).ActiveSheet
On Error GoTo 0
If oWsDest Is Nothing Then GoTo ERROR_PRICES
' determine range to be copied
With oWsSrc.Cells.CurrentRegion
Set raSrc = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)
End With
' determine destination; first available row in column B
Set raDest = oWsDest.Cells(oWsDest.Rows.Count, "B").End(xlUp).Offset(1, 0)
' perform copy
raSrc.Copy Destination:=raDest
' save prices.xlsx
oWsDest.Parent.Save
oWsDest.Parent.Close
' close DBF
oWsSrc.Parent.Close SaveChanges:=False
'restore changed settings
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
ThisWorkbook.Saved = True
' task has been completed successfully, Excel can be closed
.Quit
End With
Else
MsgBox "DBF file [" & sPath & sDBF & "] not found.", vbExclamation
End If
GoTo DONE
ERROR_DBF:
MsgBox "Error opening DBF file " & sPath & sDBF, vbExclamation
Exit Sub
ERROR_PRICES:
MsgBox "Error opening workbook " & sPath & cDestWorkBk, vbExclamation
DONE:
End Sub
This goes in the
ThisWorkbook module of
DBF_TOMORROW.xlsm
VBA Code:
Option Explicit
Private Sub Workbook_Open()
Call Copy_Tomorrows_DBF_to_PricesWBK
Me.Saved = True
Application.Quit
End Sub
Private Sub Copy_Tomorrows_DBF_to_PricesWBK()
Const cRootFolder As String = "C:\Price\" ' <<<<< change accordingly (without year!)
Const cDestWorkBk As String = "Prices.xlsx" ' <<<<< change accordingly
Dim oWsSrc As Worksheet
Dim oWsDest As Worksheet
Dim raSrc As Range
Dim raDest As Range
Dim sPath As String
Dim sDBF As String
Dim sFName As String
Dim dtDate As Date
' assign tomorrow's date
dtDate = Date + 1
' compose path for tomorrow's year
sPath = cRootFolder & Year(dtDate) & "\"
' compose file name
sDBF = "SB" & Year(dtDate) & IIf(Len(Month(dtDate)) = 1, "0" & Month(dtDate), Month(dtDate)) & _
IIf(Len(Day(dtDate)) = 1, "0" & Day(dtDate), Day(dtDate)) & ".dbf"
' check within folder on existence of file
sFName = Dir(sPath & sDBF)
If Len(sFName) > 0 Then
' change some settings for the benefit of speed
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
' open DBF file
On Error Resume Next
Set oWsSrc = Workbooks.Open(sPath & sFName).ActiveSheet
If oWsSrc Is Nothing Then GoTo ERROR_DBF
' open destination workbook
Set oWsDest = Workbooks.Open(sPath & cDestWorkBk).ActiveSheet
On Error GoTo 0
If oWsDest Is Nothing Then GoTo ERROR_PRICES
' determine range to be copied
With oWsSrc.Cells.CurrentRegion
Set raSrc = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)
End With
' determine destination; first available row in column B
Set raDest = oWsDest.Cells(oWsDest.Rows.Count, "B").End(xlUp).Offset(1, 0)
' perform copy
raSrc.Copy Destination:=raDest
' save prices.xlsx
oWsDest.Parent.Save
oWsDest.Parent.Close
' close DBF
oWsSrc.Parent.Close SaveChanges:=False
'restore changed settings
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
ThisWorkbook.Saved = True
' task has been completed successfully, Excel can be closed
.Quit
End With
Else
MsgBox "DBF file [" & sPath & sDBF & "] not found.", vbExclamation
End If
GoTo DONE
ERROR_DBF:
MsgBox "Error opening DBF file " & sPath & sDBF, vbExclamation
Exit Sub
ERROR_PRICES:
MsgBox "Error opening workbook " & sPath & cDestWorkBk, vbExclamation
DONE:
End Sub
