Copy Data From Changing Name File

[honkin]

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

 

[honkin]

This is what got returned from the macro to determine file location mate


/Volumes/[C] Shane/Price/2020

cheers

 

[honkin]

Hi GWteB
This is what the macro returned for file location mate

/Volumes/[C] Shane/Price/2020

 

[GWteB]

I am unable to save a file to the folders on the VM. I can copy files across from my Mac to the VM, but not save them directly, whether from Excel or anything else. I am hoping that will be rectified today and then happy to readdress the macro issue

If I understand correctly, the mutual drive & folder issue is resolved at present and what you are trying to achieve is as in the image, with a daily changing file name of the DBF file, am I right?

 

[GWteB]

As a first attempt, open the workbook you created as described in my post # 9, which should be in the same location as where the DBF files are located.
Open the VBE and paste the following code in a standard module. Replace the string of the sDBF constant with a proper file name of an existing DBF file.
Run the code. Does it succeed and when it does, is the data as expected or gathered in just one column? Let me know your findings.

Sub FirstAttempt()

    Const sDBF  As String = "SByyyymmdd.dbf"   ' <<<< replace with name of existing file
    
    Workbooks.Open Filename:=ThisWorkbook.Path & "/" & sDBF
End Sub

 

[honkin]

Thanks for your reply, GWteB

I had a remote session with the guys from Parallels Desktop a couple of days ago and it was determined that there is an issue with Excel with regard to saving to a VM; well at least saving certain types of files. Here's what I tried; I could create a new file and save it to the folder where the DBF files are, but I could only save it as a .csv or .pdf. If I tried to save it as any of the standard Excel formats, that previous error message pops up. The other thing was that even if I saved a file as .xlsm in the Mac and copied it across to the VM, when I opened it and input any code into the VBE, when I tried to save it, the same message appeared. So I think it was basically fighting against something which would not be resolved in the short term.

So I bit the bullet and got a copy of Excel for Windows and installed it just now. This will mean I will be able to create the macro in a file already residing in the correct location without any hiccups. So I guess now I am back to the original issue which was to write a macro which will copy all the data except the header from a file which has a different name each day. The file's name format is SB20200605.DBF...so this would be what today's file is called. Each day it is different. The data is all stored from columns A to L, though will need to be pasted into the next available row from B onwards. This is due to no date column being present in that DBF file, as the date is in the name. I can easily add the date to column A in the target file, so best to leave it blank.

I can write a VBA script to open the .xlsm file and call the macro to do the transfer. I assume it will need to firstly open the DBF file, but perhaps not; I am not sure. Once I have the macro in the file and the VBA script written, it is just a matter of scheduling it for every 2 hours, so the difficult part is a macro which will transfer the data from the DBF to the file prices.xlsm; both files will reside in the same folder...C:\Prices\2020

I hope that has recapped everything properly.
cheers

 

[GWteB]

If your DBF file meets the Ashton-Tate specifications, it should open in Excel as in the left image, i.e. split into columns.
In my approach I therefore assumed this. If the desired result should look like the picture on the right, you can use the enclosed code.

I would advise you to copy the prices.xlsx workbook and try the code on this copy.

Public Sub Copy_DBF_to_Workbook()

    Const cRootFolder   As String = "C:\Prices\"        ' <<<<< 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"

    ' check within folder on existence of file
    sFName = Dir(sPath & sDBF)
    If Len(sFName) > 0 Then

        ' 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

    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

 

[honkin]

hi GWteB

Thanks so much for this. I needed to change the file to xlsm, so amended the code to that instead of xlsx

I can say if it works or not as it says it cannot find the DBF file, yet it is sitting there as plain as day.

Any thoughts on what might be causing that?

 

[GWteB]

The "file not found" warning is launched by the code to prevent Run-time errors.
The code actually searches for just ONE file. Because of your screenshot, I understand that at least one file meets the requirements for filename formatting.
Just to be sure, you are now running Excel in the Windows VM, am I right? Then it should work (within my setup it does...).
It's even more strange, because the two used variables are clearly assigned with the proper string values. The pop-up warning tells us so, since both variables are used in the same manner to either check on file existence and to display the message (the Windows file system isn't case sensitive, so dbf equals DBF).
It shouldn't make any difference but might be worth trying to replace the next two lines of code.
Replace this:

    ' check within folder on existence of file
    sFName = Dir(sPath & sDBF)
    If Len(sFName) > 0 Then

by this:

    ' check within folder on existence of file
    Dim oFSO    As Object
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    If oFSO.FileExists(sPath & sDBF) Then
        sFName = sDBF

If this isn't going to work because of the same warning, then we have to do more investigation at your side. That could be a challenge at distance.

 

[honkin]

GWteB

Sorry to say the same warning. So I double checked and it was entirely my fault. The folder is not C:\Prices it is C:\Price. I have fixed that problem, but now it opens the DBF file and nothing happens. Both files simply remain open, but no data is transferred across.

At least it finds it, so heading in the right direction. Sincere apologies for that wrong information.

By the way, I tried it with the original ' check within folder on existence of file code and the replacement. Neither did anything except open the DBF file and sit there

cheers mate

 

[GWteB]

Both files simply remain open, but no data is transferred across.

As I understand it now, you are referring to the DBF and the Prices workbook. If so, and if no warnings appear on the screen it means the code stops for any reason after both source file (DBF) and destination file (prices.xlsx) was found and opened (in this order). There may be a few causes for this. While reading this thread more thoroughly, it seems that at some point you have changed the Prices workbook from a general workbook (xlsx) to a macro enabled workbook (xlsm). If so, then the three causes described below are the most obvious.

If the Prices workbook (as a macro enabled workbook; *.xlsm) was created within another drive or folder (in the Mac environment) and was copied or moved to its current folder (ie C:\Price\2020\ within the Windows VM environment) then dependent on settings in Excels Trust Center this workbook may have been opened in Excels so called "safe mode", which stops macro execution. As a matter of fact in such a scenario you should have been warned by a colored message bar just below Excels ribbon, but apparently the message bar did not pop up. If it did but you did not notice, note that at some point one can mark a not trusted macro enabled workbook as trusted. For details have a look over here :

Enable or disable macros in Microsoft 365 files - Microsoft Support

Improve security, evaluate and mitigate the risks of running macros, and see how to enable or disable macros for Microsoft 365.

support.office.com

Also a possible (but not likely) scenario is: your prices.xlsm workbook is "trusted" and contains a Workbook_Open event procedure in the ThisWorkbook module and is invoking Application.Stop.

Most likely is the scenario that you are running my code from the workbook to which the DBF data is to be copied, ie prices.xlsM. If this is actual the case, the code stops since the code is reopening the workbook in which that code is running, thus program flow wil be interrupted while it can never be resumed. My code however was meant to be pasted in and executed from a separate workbook (other than the prices.xlsX workbook), although I didn't mention it explicitly. It is recommended to still do this, using a (completely) different name for the macro enabled workbook from which this code is running to prevent future confusion, and run the code again. It is recommended to make a copy of the destination workbook beforehand (prices.xlsx). Please let me know your findings.