api0001 Dialog Box - VBA Assistance in Coding Save File functional

[lgray]

I've successfully used Ken Getz api0001 Dialog Box code for Importing and
Exporting files. I now need to simply open a Dialog box to locate a file,
and then save that file to a different location. It seems that it would be
simple but I simply can't get the code correct for it to function. I still
struggle with VBA but understand the basic concepts

 

[Douglas J. Steele]

Assuming you've got the code from http://www.mvps.org/access/api/api0001.htm
saved in a module, you'd use code like the four lines at the top of the page
to prompt the user and retrieve the file name:

Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
That would put the full path to the selected file into variable
strInputFileName. To make a copy of that file somewhere else, you'd then use
the VBA FileCopy statement:

FileCopy strInputFileName, "E:\Folder\Subfolder\" & Dir(strInputFileName)

The reference to Dir(strInputFileName) is to get the name of the file
without its path.

 

[lgray]

Thank you for the quick response Doug, when I plugged in the simple code
that you provided and changed the FileCopy to:
FileCopy strInputFileName, "c:\" & Dir(strInputFileName)
so that I could test it I got a debug error. What is wrong with this
statement

 

[Douglas J. Steele]

I don't see anything wrong with the statement.

What's the exact error message you're getting?

 

[lgray]

Doug,
Well, yesterday I tried to get it to work for at least an hour, and kept
getting a Debug error and then the statement I referenced below was
highlighted in the module. I just tried it now, and it worked. I don't know
what that was about, but Thank you for your help, It is working now.

You guys are great

 

[lgray]

Doug,
I've got the original code worked out and am now building in hyperlink
functionality. I can't get the syntax correct. could you please review and
see why/how I am writing this incorrectly. The hyperlink function needs to
insert the value of strOutputfileName, not the hardcoded file path that I've
used as an example.

Function SaveFileCodeWorks4()
Dim strFilter As String
Dim strInputFileName As String
Dim strOutputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.XLS")
strFilter = ahtAddFilterItem(strFilter, "doc Files (*.doc)", "*.DOC")
strFilter = ahtAddFilterItem(strFilter, "dBASE Files (*.dbf)", "*.DBF")
strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt)", "*.TXT")
strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")

strOutputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=False, _
DialogTitle:="Please select an Output Destination...", _
Flags:=ahtOFN_HIDEREADONLY)
CurrentDb().Execute "INSERT INTO Encroachment_Attachments (Attachment) " & _
"VALUES ('Attachments#" & _
"C:\Test.doc##" & _
"C:\Test.doc');", dbFailOnError
End Function

 

[Douglas J. Steele]

Is the problem that you're getting a link to c:\test.doc rather than what
you selected in the file dialog?

If Len(strOutputFileName) > 0 Then
CurrentDb().Execute "INSERT INTO Encroachment_Attachments (Attachment) " &
_
"VALUES ('Attachments#" & _
strOutputFileName & "##" & _
strOutputFileName & "');", dbFailOnError
End If

 

[lgray]

Doug,
I had the syntax slightly off. Your code worked. And again, you guys are
great. Thank you. But fair warning, I think I have one more related issue
that I might be contacting you on.

Cheers

 

[Douglas J. Steele]

I'll likely be here. <g>

 

[lgray]

Doug
I think this is the last question, and it may not be in your area of
expertise. It is more to do with sending a Hyperlink of a filepath from
word, back to a field in an Access table.
What my client is trying to do is be able to collect an assortment of
documents, some pdf, some Excel, some Word, etc that are associated with one
customer. Which is what the other code was to accomplish. One of the types
of documents is Mailmerge. I know that I can create the Hyperlink for the
original Mailmerge document, but there are definite instances where the
employees need to modify the document further, after the Mailmerge function.
What he wants to do is have a hyperlink of this modified Word document sent
back into Access and to the table that holds all of the attachments for a
particular customer.
Do you know if this needs to be programmed in Word? And if it does, where
is a good source for me to go to learn how / where to do the coding.

 

[Douglas J. Steele]

Sorry, I don't know off the top of my head, and I'm afraid I don't have time
to investigate at the moment.

Probably a good time to start a new thread and hope someone else picks it
up.

 

[lgray]

Already have. I knew this was somewhat different than the normal VBA
programming. Thank you again for all of your help

 

[Adnan]

Doug,

I benefited for your response here, too. But had a little question that I
was hioping you have the time to look at.

I am trying to replace this:

FileCopy strInputFileName, "E:\Folder\Subfolder\" & Dir(strInputFileName)


With this:

FileCopy strInputFileName, Forms!frmFilePath!AttachmentPath & Dir
(strInputFileName)

…and it’s not working. What it is, I have a little form that stays open in
hidden mode and I want to copy the file to whatever path is in
Forms!frmFilePath!AttachmentPath.

Any idea as to what I am missing here?

Thank you,
Adnan

 

[Alex Dybenko]

Hi,
try to debug and check what
?Forms!frmFilePath!AttachmentPath & Dir(strInputFileName)
equals to. Perhaps one slash is missing

Also make sure that destination file is deleted, if one exists


--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Adnan]

Exactly, I had a slash missing and got it fixed. Thank you so much sir!

Adnan