Copy Data From Changing Name File

honkin

Active Member
Joined
Mar 20, 2012
Messages
371
Office Version
  1. 2016
Platform
  1. MacOS
Hi

I need to write a macro to open a file and transfer data from one file to another; the problem is I will never know the exact source filename. Let me explain.

Data is collected in one program and it is output into a daily .DBF file. The filename is in this format, SB20200531.DBF The problem is that the last part after the SB is today's date and each day there is a new file with the filename of that date.

This will happen multiple times during the day, as the file is updated every 2 hours, so I will create a script file and a .bat file to run in Windows Scheduler, but what I need is something which will open the new file and copy all the data in that daily .DBF file copied across to the next available row in a spreadsheet called prices.xlsx which will be in C:\Price\2020. The location of the .DBF file will always be the same as well...well at least for the rest of 2020

There is also another hitch; I run Windows in Parallels Desktop on my iMac, so it is actually a virtual machine. Excel is installed on the Mac and not Windows, but the Windows folders are accessible from the Mac side. So the real address of the files is smb://Shane._smb._tcp.local/[C] Shane/Price/2020/

Is this at all possible?

Thanks so much in advance
 
GWteB

Ok, so I removed quickly recorded a macro to create the Personal Workbook in Excel, then copied and pasted the code into a new module in that workbook. The Prices.xlsx and Prices.xlsm now have no macro in them at all. So when I run the macro by opening Prices.xlsm (could be any file, but it works), it works perfectly, opening both the DBF file and Prices.xlsx and doing the necessary copy and paste and closing them both.

That is brilliant. I can now write a simply VBA script file to open that file and run the macro. Then I will write a .bat file which can be used to schedule it all to occur whenever I wish.

Thanks so much for all your help. It was a bit of a trial, but now I at least have the working macro and can proceed

Take care
 
Upvote 0

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Howdy GWteB

May I be a pain and ask one more question? How I would change the code so it looks to open the file for tomorrow's date to transfer? I just realised that due to where I am there is a time difference between me and where the data comes from, so in the end, I need one macro to open for today up until a certain time and one to do it for tomorrow's date after that time and until I catch up here. I have the vbs script file written and have it all scheduled, so could just write a new vbs script with the new macro listed and schedule it for those times when it is needed over today's date. So one macro does today and one does tomorrow.

It will naturally be in here somewhere, but the change

VBA Code:
Public Sub Copy_DBF_to_Workbook()

    Const cRootFolder   As String = "C:\Price\"        ' <<<<< change accordingly (without year!)

    Const cDestWorkBk   As String = "Prices.xlsx"

    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 current date
    dtDate = Date

    ' assign yesterday's date
'    dtDate = Date - 1

    ' compose path for current 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"

I get the feeling it will be simply a +1 somewhere but not sure enough to do it

Thanks again for all your help
 
Upvote 0
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).

ScreenShot110.png



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.

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
 
Upvote 0
g'day GEteB

Thanks so much for getting back to me.

The ultimate aim is just to have today's & tomorrow's data copied to a spreadsheet. It wouldn't bother me for it to have been the same one, but happy to do it this way

I originally created the macro in the Prices.xlsm file, then moved it to the Personal Workbook and deleted it from where it was.

This is how I scheduled the original one, which looks after today's data.

VBA Code:
Dim objExcel, objWorkbook 

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Price\2020\Prices.xlsm")
objExcel.Visible = True
objExcel.Run "Copy_DBF_to_Workbook"
objWorkbook.Close
objExcel.Quit

Set objWorkbook = Nothing
Set objExcel = Nothing

WScript.Echo "Finished."
WScript.Quit

I then just scheduled wscript.exe for whenever I needed it with the .vbs file I created listed in the arguments.

I am not 100% sure this new way will work, as I just created both workbooks and saved the macros. Each time I go to open those files manually, which I will need to do constantly, I am assuming it is the macro running in the background, as the files try to open and then close. It means I have no access to those new files currently. To be honest, I'm actually really happy with how the existing macro works and simply wanted to replicate it, but make a small change so it is using tomorrow's date and then schedule it accordingly. I noticed that for tomorrow's date, you have
VBA Code:
dtDate = Date + 1
in the new macro. With the original macro, is that the only change which needs to be made so that it looks to open tomorrow's DBF file?

I'm in awe of how much knowledge you have in Excel. I want to slowly go through each part of the code to digest what it targets and what it does. I've written some pretty basic VBA stuff, but nothing like this.

Thanks a bunch. As soon as I duplicate the original macro and change the date it is searching for, I should be 100% automated; well, all bar the actual downloading of the data from online. You don't happen to know a good macro program to automate a 3rd party app...meaning to open it and perform certain tasks before closing it again do you? This app basically just connects to a server and downloads either today's or tomorrow's data, depending on which date is selected.

Take care
 
Upvote 0
Hi honkin,

Glad to help and I appriciate your feedback.

You don't happen to know a good macro program to automate a 3rd party app...meaning to open it and perform certain tasks before closing it again do you? This app basically just connects to a server and downloads either today's or tomorrow's data, depending on which date is selected.
Although I generally find my way with VBA, I don't have any experience with (web) servers and downloading data in that way. Simply wasn't in the need for it.

I originally created the macro in the Prices.xlsm file, then moved it to the Personal Workbook and deleted it from where it was. This is how I scheduled the original one, which looks after today's data.
This is a way of doing it and I like it. Launching a separate instance of Excel, opening the desired workbook and running the desired macro within that workbook. You said this was your original way but you then moved the macro to your Personal Macro Workbook. Just an observation, but how did you got things going using .vbs? The PMW does not open automatically when Excel is launched with the CreateObject function so it has to be opened with code like a normal workbook (while it isn't), and macros will not run. It could be caused by the difference between Excel versions, I don't know but although some users find it handy, personally I prefer not to use the PMW at al. As with everything the PMW has its pros and cons.

I am not 100% sure this new way will work, as I just created both workbooks and saved the macros. Each time I go to open those files manually, which I will need to do constantly, I am assuming it is the macro running in the background, as the files try to open and then close. It means I have no access to those new files currently.
Your assumption is partly correct. After you confirmed in your post # 22 that the copy process went as it should (quote: "So when I run the macro by opening Prices.xlsm (could be any file, but it works), it works perfectly, opening both the DBF file and Prices.xlsx and doing the necessary copy and paste and closing them both."), I made the final changes to start and close the process automatically. The batch file enables running it all in the background. I assumed this was the intention, but of course I could have asked. You do have access to such a file although not in the normal way. That's one of the drawbacks of my approach, but I did mention that in my previous post. In retrospect, this may not have been the best solution in your situation. It's useless to offer someone a bicycle if it's indicated he's needing a truck, so I may have a better way.

I noticed that for tomorrow's date, you have
dtDate = Date + 1
in the new macro. With the original macro, is that the only change which needs to be made so that it looks to open tomorrow's DBF file?
Yes it is, but you might be interested in the alternative version below.

I suggest modifying your VB script in a way it accepts a parameter. That way you can call your script within Windows Task Scheduler (or through a batch file) and pass it that parameter. After the script has launched Excel, the script passes this parameter to the VBA procedure. In connection with this I have modified my original code in such a manner that it also accepts a parameter. In this way, only one procedure is needed to copy the DBF data from yesterday, today, tomorrow or whatever day before or after today's date to the destination workbook. In this way the previous mentioned drawback no longer exists, the workbook in which the code is running can be opened in a normal way. So you just need one workbook and one .vbs. The code is expected to run within the Prices.xlsm workbook, since I'm not able to test it with the PMW as explained before. To schedule your .vbs within Windows Task Scheduler for copying today's DBF data nothing has changed, for tomorrow's DBF data simply ad a space and a 1 behind the name of your .vbs within the parameters input box, example:

wscript.exe c:\price\2020\yourscript.vbs 1

The parameter will be added to today's date and therefore has to be numeric (either positive or negative). The parameter may consist of more than one contiguous characters. Anything else (ie non-numeric characters) will result in today's date. The parameter may be omitted, which also results in today's date.

Hopefully this suits your needs. If it doesn't let me know. Stay healthy and safe!

your amended VB script:
VBA Code:
Dim objExcel, objWorkbook
Dim oWScrArgs, sDayDiff

set oWScrArgs = WScript.Arguments
If oWScrArgs.Count = 0 then
    sDayDiff = 0
Else
    sDayDiff = oWScrArgs(0)
End If

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Price\2020\Prices.xlsm")
objExcel.Visible = True
objExcel.Run "Copy_DBF_to_Workbook", sDayDiff
objWorkbook.Close
objExcel.Quit

Set oWScrArgs = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing

WScript.Echo "Finished."
WScript.Quit


code to run within a standard module in Prices.xlsm
VBA Code:
Public Sub Copy_DBF_to_Workbook(Optional ByVal argDayDiff As Variant)

    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
    Dim iDayDiff        As Integer

    ' check parameter for validity, it should be numeric since it has to be added to today's date
    On Error Resume Next
    If Not IsMissing(argDayDiff) Then
        iDayDiff = CInt(argDayDiff)
    End If
    On Error GoTo 0

    ' assign requested date
    dtDate = Date + iDayDiff

    ' compose path for current 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
        End With
       
        ' prevent potentional warnings when workbook is closed
        ThisWorkbook.Saved = True

    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
 
Upvote 0
Hi GWteB

Thanks again for your help

"how did you got things going using .vbs" It appears I had actually not deleted the macro from Prices.xlsm, so I can only assume that it ran it from there rather than the PMW. I am not really able to test the macro for tomorrow's data, as when I run the new one from Prices.xlsm, it copies only today's data. It also means I can't test the vbs until it is in the task scheduler.

As indicated, when I run the macro, it works without even appearing to open the files, which is nice. The data for today is copied flawlessly. When I run it using the new vbs code, the same result; data for today is copied well. So when I put it in the task scheduler and ran it, it also worked copying the data for today. When I add the space 1 to it, nothing happens at all; no data is copied at all, so I am stumped.

Any thoughts on what next.

Screen Shot 2563-06-14 at 09.12.43.png


I have tried it both with and without the opening and closing ""

cheers
 
Upvote 0
Hi GWteB

OK, it now appears everything is fine. For whatever reason, possibly when I ran the task with the opening and closing "" on it, possibly because I ran it with the space 1 after the closing "; I am not sure, but I was actually not even able to download the data from the net. When I tried to manually open the DBF, a message said it was already open in Excel and was locked. After checking Task Manager, it was found there were 4 instances of Excel running, even though it was not really open. I closed all 4 and was able to grab the data I needed, but more importantly, when I just ran the task with the space 1 on it, it copied the data from tomorrow to the file.

Yeehar mate. Sorry it was such a trial. That was doing my head in a bit. Not sure what kept those instances of excel open, but something did and now it seems to have righted itself.

I'll keep monitoring over the next few days, but it looks to be spot on now. All I need do is to schedule the task without the space 1 for when I want tooday and with the space 1 for when I want the following day

Thanks again mate. Very much appreciated

Regards
 
Upvote 0
When I tried to manually open the DBF, a message said it was already open in Excel and was locked. After checking Task Manager, it was found there were 4 instances of Excel running, even though it was not really open.
If you've used the commandline from my post #23 then Excel was launched in the background. If during that session an involved file could not be found, Excel wasn't able to quit properly due to a (not visible) screen message waiting for a click on its OK button.

I made some amendments to my VBA code and some enhancements to the VB script, so such behavior is not likely anymore.
  • The vbs takes arguments which are kind of self-explanatory. Arguments may be used in any order. Some arguments have default values and may therefore be omitted. If not entered any argument at all a syntaxis screen will apear.
  • File names and their path are no longer "hard coded" within the VBA code, they've been moved to the VB script. Those names are used on default. Other names can be enforced (by using the /MacroFile or /TargetWorkbook arguments). Their path is obtained through the scripts folder.
  • Screen messages appear only if Excel is visible on screen. If Excel is running in the background (by using the /Quiet argument), screen messages are suppressed. Messages will be written to a log file on default unless it's explicitly disabled (by using the /NoLogging argument).
  • Excel will always be closed afterwards, unless it's explicitly told not to (by using the /RemainOpenOnExit argument).
To make this work the macro file (prices.xlsm), the source DBF file(s), the target workbook (prices.xlsx) and the VB script file must all be located in the same folder on disk. The VB script determines its current path (first two lines of code in the MAIN section) and passes this path on to the VBA code within the macro enabled workbook (default: prices.xlsm). All other necessary parameters are also passed on to the VBA code. Because the parameters cannot be named, they must be passed on to the VBA code in a specific(!) order.
In this way you may be able to use it with more flexibility. The moment you move your VB script along with the Excel workbooks and the DBF files to another folder (for example to C:\Price\2021\ for the next year) then you only have to adjust the scheduled tasks and any batch file if used.

Note that due to Windows' file association you're able to launch a VB script directly within Task Scheduler. Enter the scripts filename (including drive and path) into the Program/script: box and enter the arguments in the Add arguments (optional): box. Wscript.exe will be launched automatically.
A typical statement within Windows Task Scheduler for today's day would be like:
C:\Price\2020\yourscript.vbs /Quiet
A typical statement within Windows Task Scheduler for tomorrow's day would be like:
C:\Price\2020\yourscript.vbs /Quiet /DayDiff:1

For launching a VB script using a batch file the START command is needed, as in (for example):
START C:\Price\2020\yourscript.vbs /RemainOpenOnExit /TargetWorkbook:SomeOtherPrices.xlsx


VB Script
:
VBA Code:
'    ==============================================
'      VB script to interact with Excel VBA macro
'    ==============================================


    Dim objExcel, objWorkbook, oColArgs
    Dim sMeName, sMePath
    Dim bResult, bQuiet, bNoLogging, bRemainOpenOnExit
    Dim sMacroFile, sMacroName, sTargetWorkbook
    Dim iDayDiff

Private Sub GetArgs()
    If oColArgs.Exists("MacroFile") And Len(CStr(oColArgs.Item("MacroFile"))) > 0 Then
        sMacroFile = CStr(oColArgs.Item("MacroFile"))
    Else
        sMacroFile = "prices.xlsm"
    End If
    If oColArgs.Exists("MacroName") And Len(CStr(oColArgs.Item("MacroName"))) > 0 Then
        sMacroName = CStr(oColArgs.Item("MacroName"))
    Else
        sMacroName = "Copy_DBF_to_Workbook"
    End If
    If oColArgs.Exists("TargetWorkbook") And Len(CStr(oColArgs.Item("TargetWorkbook"))) > 0 Then
        sTargetWorkbook = CStr(oColArgs.Item("TargetWorkbook"))
    Else
        sTargetWorkbook = "prices.xlsx"
    End If
    If oColArgs.Exists("DayDiff") Then
        If IsNumeric(oColArgs.Item("DayDiff")) Then
            iDayDiff = CInt(oColArgs.Item("DayDiff"))
        Else
            iDayDiff = CInt(0)
        End If
    Else
        iDayDiff = CInt(0)
    End If
    If oColArgs.Exists("Quiet") Then
        bQuiet = True
    Else
        bQuiet = False
    End If
    If oColArgs.Exists("NoLogging") Then
        bNoLogging = True
    Else
        bNoLogging = False
    End If
    If oColArgs.Exists("RemainOpenOnExit") Then
        bRemainOpenOnExit = True
    Else
        bRemainOpenOnExit = False
    End If
End Sub

Private Sub Usage()
    WScript.Echo "USAGE:" & vbCrLf & "=====" & vbCrLf & "WScript.exe " & sMeName & "  [/MacroFile:{Name}]  [/MacroName:{Name}]  " & _
                 "[/TargetWorkbook:{Name}]  [/DayDiff:{Number}]  [/Quiet]  [/NoLogging]  [/RemainOpenOnExit]" & vbCrLf & vbCrLf & _
                 "DEFAULTS:" & vbCrLf & "=======" & vbCrLf & _
                 "Current Folder" & vbTab & ": " & sMePath & "  (cannot be changed)" & vbCrLf & _
                 "/MacroFile" & vbTab & ": prices.xlsm" & vbCrLf & _
                 "/MacroName" & vbTab & ": Copy_DBF_to_Workbook" & vbCrLf & _
                 "/TargetWorkbook" & vbTab & ": prices.xlsx" & vbCrLf & _
                 "/DayDiff" & vbTab & vbTab & ": 0" & vbCrLf & vbCrLf & _
                 "OPTIONAL:" & vbCrLf & "=======" & vbCrLf & _
                 "/Quiet" & vbCrLf & _
                 "/NoLogging" & vbCrLf & _
                 "/RemainOpenOnExit" & vbCrLf & vbCrLf & _
                 "EXAMPLE:" & vbCrLf & "======" & vbCrLf & "wscript.exe " & sMeName & " /Quiet /DayDiff:1" & vbCrLf & vbCrLf & _
                 "... uses default files (in current folder), uses default macro, suppress screen messages, keeps a log file and uses tomorrow's date."
End Sub

'   ========== MAIN SCRIPT ==========

    sMeName = WScript.ScriptFullName
    sMePath = Left(sMeName, (InStrRev(sMeName, "\") - 1)) & "\"

    If WScript.Arguments.Count = 0 Then
        Call Usage
        WScript.Quit
    End If

    Set oColArgs = WScript.Arguments.Named
    Call GetArgs

    Set objExcel = CreateObject("Excel.Application")
    If Not bQuiet Then
        objExcel.Visible = True
    End If
    Set objWorkbook = objExcel.Workbooks.Open(sMePath & sMacroFile, , True)
   
    ' RUN EXCEL MACRO with necessary parameters IN A REQUIRED ORDER!
    bResult = objExcel.Run(sMacroName, bQuiet, bNoLogging, bRemainOpenOnExit, sMacroFile, sTargetWorkbook, iDayDiff, sMePath)
    objWorkbook.Close
   
    If Not bRemainOpenOnExit Then
        objExcel.Quit
    Else
        objExcel.Visible = True   
    End If

    Set oColArgs = Nothing
    Set objWorkbook = Nothing
    Set objExcel = Nothing

    If Not bQuiet Then
        If bResult Then
            WScript.Echo sMeName & ":  Finished without errors."
        Else
            If bNoLogging Then
                WScript.Echo sMeName & ":  An error has occurred!  (See logfile)"
            Else
                WScript.Echo sMeName & ":  An error has occurred!"
            End If
        End If
    End If
    WScript.Quit


VBA code in Prices.xlsm
VBA Code:
Public Function Copy_DBF_to_Workbook(ByVal bQuiet As Boolean, ByVal bNoLogging As Boolean, ByVal bRemainOpenOnExit As Boolean, _
                                     ByVal sMacroFile As String, ByVal sTargetWbk As String, _
                                     ByVal iDayDiff As Integer, ByVal sCurFolder As String) As Boolean

    Dim oWsSrc          As Worksheet
    Dim oWsDest         As Worksheet
    Dim raSrc           As Range
    Dim raDest          As Range
    Dim sDBF            As String
    Dim sFName          As String
    Dim dtDate          As Date
    Dim sLOG            As String
    Dim sMsg            As String

    ' add/subtract day difference to/from current date
    dtDate = Date + iDayDiff

    ' compose DBF 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"
    ' compose LOG file FullName
    sLOG = Left(ThisWorkbook.FullName, (InStrRev(ThisWorkbook.FullName, ".") - 1)) & "_log.txt"

    ' check within folder on existence of file
    sFName = Dir(sCurFolder & 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 for ReadOnly
        On Error Resume Next
        Set oWsSrc = Workbooks.Open(sCurFolder & sFName, ReadOnly:=True).ActiveSheet
        If oWsSrc Is Nothing Then GoTo ERROR_DBF

        ' open destination workbook, suppress warning on opening for ReadOnly (wbk may be in use)
        Application.DisplayAlerts = False
        Set oWsDest = Workbooks.Open(sCurFolder & sTargetWbk).ActiveSheet
        Application.DisplayAlerts = True
        On Error GoTo 0
        If oWsDest Is Nothing Then GoTo ERROR_PRICES
        If oWsDest.Parent.ReadOnly Then GoTo ERROR_PRICES_RO

        ' 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)

        ' copy DBF data across
        raSrc.Copy Destination:=raDest

        ' enter DBF date in column A
        Set raDest = oWsDest.Cells(oWsDest.Rows.Count, "A").End(xlUp)
        Set raDest = raDest.Offset(1, 0).Resize(raSrc.Rows.Count, 1)
        raDest.Value = dtDate

        'restore changed settings
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
            .Calculation = xlCalculationAutomatic
        End With

        ' save destination workbook
        oWsDest.Parent.Save

        ' close source and target files unless otherwise is requested
        If Not bRemainOpenOnExit Then
            oWsSrc.Parent.Close SaveChanges:=False
            oWsDest.Parent.Close
        End If
        sMsg = "Content of file [" & sCurFolder & sFName & "] has been successfully copied to [" & sCurFolder & sTargetWbk & "]"

        ' Return result to VB script
        Copy_DBF_to_Workbook = True

    Else
        sMsg = "DBF-file [" & sCurFolder & sDBF & "] not found"
    End If
    GoTo DONE

ERROR_DBF:
    sMsg = "Error opening source file [" & sCurFolder & sDBF & "]"
    GoTo DONE

ERROR_PRICES:
    sMsg = "Error opening destination workbook [" & sCurFolder & sTargetWbk & "]"
    GoTo DONE

ERROR_PRICES_RO:
    sMsg = "Destination workbook [" & sCurFolder & sTargetWbk & "] is ReadOnly and cannot be saved"

DONE:
    ' warnings & logging
    If Copy_DBF_to_Workbook Then
        If Not bQuiet Then
            MsgBox sMsg, vbInformation, ThisWorkbook.Name
        End If
        If Not bNoLogging Then
            sMsg = ThisWorkbook.Name & ":success; " & sMsg
            Call WriteToLog(sLOG, sMsg)
        End If
    Else
        If Not bQuiet Then
            MsgBox sMsg, vbExclamation, ThisWorkbook.Name
        End If
        If Not bNoLogging Then
            sMsg = ThisWorkbook.Name & ":FAILURE; " & sMsg
            Call WriteToLog(sLOG, sMsg)
        End If
    End If

    ' prevent potentional warning on workbook close
    ThisWorkbook.Saved = True
End Function


Private Sub WriteToLog(ByRef argLogFullName As String, ByRef argStrLine As String)

    Const ForAppending  As Long = 8

    Dim oFSO    As Object
    Dim oFile   As Object
    Dim oTxtStr As Object
    Dim sTime   As String

    sTime = GetTimeFormatLocale
    If Len(sTime) = 0 Then
        sTime = Format(Now, "yyyy-mmm-dd at hh:mm:ss")
    End If
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    If Not oFSO.FileExists(argLogFullName) Then
        Set oTxtStr = oFSO.CreateTextFile(argLogFullName)
        oTxtStr.Close
    End If
    On Error Resume Next
    Set oTxtStr = oFSO.OpenTextFile(argLogFullName, ForAppending)
    On Error GoTo 0
    If Not oTxtStr Is Nothing Then
        oTxtStr.WriteLine (sTime & " - " & argStrLine)
        oTxtStr.Close
    End If
    Set oTxtStr = Nothing
    Set oFile = Nothing
    Set oFSO = Nothing
End Sub

Private Function GetTimeFormatLocale() As String
    Const cRegPath      As String = "HKEY_CURRENT_USER\Control Panel\International\"
    Dim oWscrSh         As Object
    Dim sRegKey         As String
    Dim sShortDate      As String
    Dim sTimeFormat     As String
    Set oWscrSh = CreateObject("WScript.Shell")
    sRegKey = cRegPath & "sShortDate"
    If RegKeyExists(oWscrSh, sRegKey) Then sShortDate = RegKeyRead(oWscrSh, sRegKey)
    sRegKey = cRegPath & "sTimeFormat"
    If RegKeyExists(oWscrSh, sRegKey) Then sTimeFormat = RegKeyRead(oWscrSh, sRegKey)
    If Len(sShortDate) = 0 Or Len(sTimeFormat) = 0 Then
        'do nothing, return empty string
    Else
        GetTimeFormatLocale = Format(Now, sShortDate & " at " & sTimeFormat)
    End If
    Set oWscrSh = Nothing
End Function

Private Function RegKeyExists(ByRef oWshl As Object, ByRef RegKey As String) As Boolean
    On Error GoTo PITTY
    oWshl.RegRead RegKey
    RegKeyExists = True
    Exit Function
PITTY:
    Err.Clear
End Function

Private Function RegKeyRead(ByRef oWshl As Object, ByRef RegKey As String) As String
    On Error Resume Next
    RegKeyRead = oWshl.RegRead(RegKey)
End Function
 
Last edited:
Upvote 0
hi GWteB

Thanks so much for the revision.

They all run well just directly from Windows Task Manager without the use of a batch file.

I haven't noticed whether this is still the case, but sometimes when I found nothing was copied, I would look in Task Manager and see 2 or 3 incidents of Excel open in the background, or even the program where the data comes from running in the background. It seems that every now and then, task manager doesn't close the appropriate program after the task is done. I will monitor how this new code performs on that front over the next day or so. It works so well when it works, it's just frustrating to come back after a few hours away and find that a couple of scheduled tasks didn't run and then find Excel open 3 times in the background. Hopefully this new code doesn't have any of those issues, though it probably more like the task manager not closing Excel properly. I have now set the tasks to each stop if they have been running longer than 4 minutes. It takes only a few seconds for the data to be transferred, having watched the tasks work in real time. Stopping the task after 4 minutes doesn't necessarily close any occurrences of Excel, though.

Again, thanks so much for your help. I'll just watch it for a day or so

Take care
 
Upvote 0
Windows Task Manager does nothing more (or less) than starting the script. The script must ensure that Excel is properly closed again. I will hear from you if everything went according to plan.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top