File Dialog-Browse/Save/Append

[andycl]

I need to be able to select a file (.txt), move the file to a different directory; however, if a similar file is in that directory append to it. I use similar because the file names will not have the exact same filename but similar. i.e myfile12 is already in the directory and the file I am moving is myfile123 and its contents need to be added to the existing myfile12.

I've explored file dialog for browsing and saving but am not having any good fortune putting it all together. Any place or pointers to start with are appreciated.

[OBP]

I am assuming that you are working with text files?
If so you can open the destination file in Append mode (instead of input mode as in your previous question) and write a string containing the new data in one go, but you obviously have to put the data in the string first.
You will obviously also need to establish the similarity using VBA If/then/else.

Do you have the code to Browse or do you really need to do that if the Folders you use are fixed?

[andycl]

What do you mean by fixed? Files are already in that directory so why have a browse feature? This is just something that is being requested for so I'm trying to incorporate it. If a user gets the external files once but something was left out, they go back and get what was left out it and it needs to bed added to the existing file (R0). The files being moved are the R0 files. And the importing you helped with previously is where they are imported and are now R1 tables ready for revisions to be made. And eventually when all revisions have been made I need a compare feature to export a text file of Delta's between R0 and R1.

Different question. I have never used nor seen where you can be linked to a table externally and make revisions to the file via a form. The tables must be physically contained within Access. Right or wrong?

[OBP]

Well there is not a point in browsing if they are always in the same folder, but I do have to the VBA for it.
As to your question Access can link to other data tables like SQL, but it requires connections that I don't normally deal with.
Like I said you can use the Append version of File Open to append the data.

[andycl]

Ok thanks, if you have something where browse and save as are incorporated together that would be helpful. I never can get the selected files to pass to the save as dialog option. The browse feature isn't necessarily for the first set of files but in the event there is a second file that should be including have the browse option to move from one directory to the other and append to the existing file that is already at that path.

[OBP]

Take a look at this simple database, I have extracted code from a much bigger one.
This works for me but you may need to set references in the VBA Editor's References Library, I have and excel sheet which shows references that need setting if you should need it.
Open Form 1 and click the Browse button.

The actual code is in a module.
There is some othe quite useful code in the database that came along with the Modules that I imported.

ps let me know if you need help with the Append routine.

[andycl]

Thanks for the db. I was having success getting the browse function to work correctly. It is taking that file and saving to another filepath that has me hung up. I will see if I can pass the selected file to the save as file dialog option. Thanks again.

[andycl]

The browse and select file functionality is working fine. I can select multiple files and display the file path in a list box. I now need to take those files and have the ability to save them to a different directory. Filecopy seems logically and if I could define the destination path I'd be done. However, I need to be able to browse to any location and copy the files. This is also where I will need to start the if/then/else statements for appending to existing files.

[andycl]

I got somewhere just not all the way there. I tried two different methods but never could get the files in the list box to save to a folder. This really needs to have the append criteria added but I need to have the saving portion down before trying that. I received "object does not except this property or method." I'm not calling the selected items correctly from the list box. The first method is the following:

Private Sub saveFileAs_Click()

  'The following lines of code use a network path for the source file :
Dim sDest As String
Dim varFile As Variant


sDest = CurrentProject.path & "\Testbed\"


For Each varFile In Me.FileList
FileCopy varFile, sDest
Next varFile


End Sub

The second method used two list box. One (InvisibleFileList) to return the full directory path and the second list box (FileList) to return the filename only. I received "path not found." The save command is as follows:

Public Sub SaveAttachments()



    Dim fileDestination As String
    Dim i As Long


    'Update this to the correct folder, be sure to include the ending \
    fileDestination = CurrentProject.path & "\Testbed\"


    For i = 0 To Me.FileList.ListCount - 1
        FileCopy Me.InvisiblePathList.ItemData(i) & "\" & Me.FileList.ItemData(i), fileDestination & Me.FileList.ItemData(i)
    Next i


End Sub

[OBP]

If you are just copying/moving the file to a new directory you can use

Name "C:\Users\Ron\SourceFolder\Test.xls" As "C:\Users\Ron\DestFolder\TestNew.xls"

The append method means opening the current file as we did for import and put the data in a string and then use File Append instead of File output to append it to the other file.

Why would you copy all the files from one directory to another each time, will you then delete them from the original file.
The browse method I provided identifies a single file and places it an unbound field for using wih your second method of copy.

[andycl]

The files are exported to a default location which cannot be changed. I'm moving the files to where the DB and any associated files for the DB will live. The list box and multiselect option is what I need to go for in this case. What you provided does work great and I will use that for single file browse select. So thank you. The destination folder will not always be the same so I was attempting to use CurrentProject.Path but keep getting file path not found . It does seem like a "what's the point" process but it's something I'm having to do.

[OBP]

Can you post what you have and what you have tried, I may have some old code for List box manipulation, but I would have thought that an Array holding file paths would do just as well.
I will test what you have.

[andycl]

Let me know if this version doesn't work for you. Thanks

[OBP]

OK, I have identified a couple of problems with what you are trying.
1. Your working through the file list in the Save VBA is not corrrect, I have that working now using this code
Dim ctlList As Control, varItem As Variant

' Return Control object variable pointing to list box.
Set ctlList = Me.FileList
' Enumerate through selected items.
For varItem = 0 To ctlList.ListCount - 1
' Print value of bound column.
MsgBox ctlList.ItemData(varItem)
'FileCopy ctlList.ItemData(varItem), sDest
Next varItem

Obviously the msgbox is not necessary I am just using to identify that the code is working.

2. You are trying to use the complete Path of the file you want to save, instead of the File Name, if you use File Scripting you can use GetFileName to get just the File's name for the file list.
You then have to add that to sDest to complete path for the file copy.
I will replace your browse version with the one that I use with File scripting and if it works OK I will post it on here tomorrow.

ps actually we could just use the right most characters of you file list to get the filename. I will try that first as it means that you can retain your get file code.

[OBP]

Well that went better than I thought, here is the code

'The following lines of code use a network path for the source file :
Dim sDest As String
Dim varFile As Variant

sDest = CurrentProject.path & "\TestFolder\" ' need to change this back to your folder
Dim ctlList As Control, varItem As Variant, posn As Integer, filename As String

' Return Control object variable pointing to list box.
Set ctlList = Me.FileList
' Enumerate through selected items.
For varItem = 0 To ctlList.ListCount - 1
' Print value of bound column.
MsgBox ctlList.ItemData(varItem)
For x = 1 To Len(ctlList.ItemData(varItem))
If Mid(ctlList.ItemData(varItem), x, 1) = "\" Then posn = x
Next x
filename = Right(ctlList.ItemData(varItem), Len(ctlList.ItemData(varItem)) - posn)
MsgBox filename
FileCopy ctlList.ItemData(varItem), sDest & filename



Note the msgboxes are not actually needed, it just shows you what is going on.
Also the change of folder name to suit my folder set up, you need to change it back to yours.
I have attached my version of the database.

[andycl]

Thanks for pointing out where I was going wrong. I'll look at what you have attached and go from there. I should be able to incorporate the the file append process into this. Thanks again.

[andycl]

Hey OBP, I'm trying to get started with the append routine. The originally exported file is a dat file. I'm unaware that it is even possible to open and write to a dat file with VBA. I tried reading from it and it never would work. So I am taking the files that are selected and creating a "_Temp.txt" that I can I use in the append routine. I have it copying both files like I want but the text file gets copied and named as _Temp.txt only and leaves the rest of the filename off from the ctlList. Please see if you see where that is happening because I am overlooking it. Thanks

For varItem = 0 To ctlList.ListCount - 1        ' Print value of bound column - used for testing purposes.
        'MsgBox ctlList.ItemData(varItem)
        For x = 1 To Len(ctlList.ItemData(varItem))
        If Mid(ctlList.ItemData(varItem), x, 1) = "\" Then posn = x
        Next x
        filename = Right(ctlList.ItemData(varItem), Len(ctlList.ItemData(varItem)) - posn)
        ' Print value of bound column - used for testing purposes.
        'MsgBox filename
        strExt = filename
        'MsgBox filename, vbInformation, "Testing"
        ' Replace the extension with _Temp.txt for a new file name
        strFileTemp = Replace(ctlList.ItemData(varItem), strExt, "_Temp.txt")
        ' Copy .txt files
        FileCopy ctlList.ItemData(varItem), sDest & strFileTemp
        ' Reset file extension .dat
        filename = Right(ctlList.ItemData(varItem), Len(ctlList.ItemData(varItem)) - posn)
        FileCopy ctlList.ItemData(varItem), sDest & filename

[OBP]

Taking a quick look I would say that this line

strFileTemp = Replace(ctlList.ItemData(varItem), strExt, "_Temp.txt")

is replacing the file name completely instead of appending the _temp.txt to the left part of the file name.

so perhaps you need to identify the left part of the jilename

strExt

after you have identified the right part here

filename = Right(ctlList.ItemData(varItem), Len(ctlList.ItemData(varItem)) - posn)

by finding the "." and using the left() function to return the filename without the extension on it.

This would then become this

strFileTemp = Replace(ctlList.ItemData(varItem), strExt, leftpart of strExt & "_Temp.txt")

where leftpart of strExt is the actual file name less the extension.

[andycl]

I was able to get the file names copied together. Thanks for the suggestion. Am I thinking about this correctly? The files being copied have similar file names i.e file1, file1a, file2, file2a and so on. If file1 exists and file1a is being copied, the contents of file1a should be merged to file1. If file2 exists and file2a is being copied, the contents of file2a should be merged to file2. The header rows of file1a should be omitted as they exist in file1. So to do this it should be something like. If file1 exist, open file1a and store data string, open file1 (destination file) in append mode, and add the data string to it. Thanks

[OBP]

Yep, spot on.
What do they use the files for?
Once you have the data in Access why do they need them?