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

 

[GWteB]

It depends on two matters:
- does Excel accept the DBF binary?
- is smb://Shane._smb._tcp.local/[C] Shane/Price/2020/ within Excels reach?
The first you can manually check, the latter also by copying (or saving) an Excel workbook on to that location and subsequently opening that workbook in order to check (within VBE) the ThisWorkbook.Path property.

 

[honkin]

Thanks for the reply GWteB

If by "accept the DBF binary" you mean, can Excel open the files, then yes; I have it set as the default to open DBF files in both MacOS and Windows.

The second part now has me stumped. I can right click any of the DBF files from either Finder on my Mac or Windows Explorer and the default program shows as Excel, yet when I am within Excel and click Open and navigate to that folder, all DBF files are greyed out. If that is going to be a problem, I will need to look to a program like QGIS to do a conversion to Excel format first.

Any thoughts on why the files are easily opened by Excel from a viewing platform like Finder or Explorer, but not from within Excel itself?

cheers

 

[GWteB]

Any thoughts on why the files are easily opened by Excel from a viewing platform like Finder or Explorer, but not from within Excel itself?

Not at all, but I don't have a Mac. In my situation (Excel 2013 on Windows) I'm able to open a DBF from within Excel, nothing is greyed out. If Finder opens Excel with your DBF due to the file association something tells me it should work. Did you already check the ThisWorkbook.Path property within the VBE of a workbook stored on smb://Shane._smb._tcp.local/[C] Shane/Price/2020/ ?

 

[GWteB]

You might consider checking this site out.

How to Convert DBF to XLS

To convert .dbf database files to XLS or XLSX Excel format, you can open the file in Excel directly and then save it in the file format you want. Alternatively, you can use a utility app to convert your files. A third option is to upload the files to a conversion website to convert them for free.

smallbusiness.chron.com

It reads: "Excel has a built-in converter that can open a DBF file and create a spreadsheet from it. You can convert any DBF database file, whether from dBase or FoxPro, in Excel directly without the need for other software. To take advantage of this feature, launch the Excel program on your Windows for Mac OS X or PC. Click the “File” tab and select “Open.” Click the “Files of Type” drop-down list; by default it reads, “All Excel Files.” Select “dBase Files” or “All Files.” Use the file browser to locate the dBase 4 format file you want to open. Double-click the file to open it, or select it with a single click and click the “Open” button. Excel opens the file and automatically formats the dBase fields into separate spreadsheet columns with headings created from the dBase field names. Note that though Excel can open DBF files, it cannot save files in that format."

although at the bottom it reads:
"Microsoft Excel Mac Edition can't open DBF files. You can download the free Apache OpenOffice suite, use its Calc module to convert DBF to XLS and open the resulting workbook in Excel."

 

[honkin]

That Files of Type drop down list is no longer part of Excel for Mac 2016, unfortunately, so that option is out. All files which Excel can open are shown automatically, but for whatever reason, DBF files remain greyed out.

ThisWorkbook.Path property within the VBE reveals nothing of value

I even tried using =CELL(“filename”) in one of the files and got back \Volumes\SB20200206.DBF returned

Sorry I can't be of any more help

 

[GWteB]

You're showing the static properties, I meant the dynamic one, ie

This path can be used for trying opening a DBF by VBA. Nothing ventured, nothing gained. We have to start somewhere, right?

 

[honkin]

Have tried this two ways' I created the macro and saved it in the Personal Workbook and run it; I then created it in the actual DBF file and ran it from there. Neither way gives any result. I cannot save the macro in the DBF file for obvious reasons. I never get the bottom section of your screenshot with the Expression, Value & Type showing.

Again, apologies as am relatively new to VBA macros, so the procedure to do this simple task is not something I am familiar with

 

[GWteB]

Again, apologies as am relatively new to VBA macros, so the procedure to do this simple task is not something I am familiar with

No worries, we will work it out!

Have tried this two ways' I created the macro and saved it in the Personal Workbook and run it.

That's not of use since the macro has to be in a workbook in the exact same location from which we are trying to pull the DBF (in future, if things run smoothly....)

I then created it in the actual DBF file and ran it from there. Neither way gives any result. I cannot save the macro in the DBF file for obvious reasons.

That's correct, macros need to be saved in a XLSM workbook. Try to open a new (blank) workbook, save it with SaveAs as an Excel Macro-Enabled Workbook (*.xlsm) on the drive and folder location in which the DBF files are located. Next, within VBE click Menu > View > Locals Window to get (using your words) "the bottom section". Next, within VBE click Menu > Insert > Module, paste this code in the right hand pane and run it (F5 key). See what the message tells us.

Sub Check()
    Dim sPath   As String
    Dim sFile   As String
    sPath = ThisWorkbook.Path
    sFile = Dir(sPath & Application.PathSeparator)
    MsgBox "ThisWorkbook is in folder: " & sPath & vbCrLf & _
           "First file in this folder is: " & sFile
End Sub

For now I'm offline for a while, over here it's far beyond midnight ...

 

[honkin]

Howdy GWteB

Am wondering what happened to my reply I posted yesterday which had a couple of screen grabs. It appears to have been removed, yet no message in my inbox to indicate anything odd.

I have a support session scheduled with Parallels Desktop in a few hours, so once I get proper access to the folders on the Windows VM fixed, I can check back in continue, if you're OK with that.

Since the post is gone, I can briefly explain, 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

cheers