Word2003/MSAccess(2003) and "Using GetSaveAsFilename() in Access"

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Word2003/MSAccess(2003) and "Using GetSaveAsFilename() in Access"

Post by ChrisGreaves »

This short thread provided a partial answer to a problem I have, but I would like to (a) get more background and (b) make my code more robust.

In a simple Word2003/VBA procedure I open a db1.mdb and immediately try to save it as db2.mdb.
My Word2003/VBA code fails in trying to save the (updated) db2.
proje004.png
I have implemented (attached zip) the procedure and code mentioned in the thread above, but now find that the "GetSaveAsFileName" function is causing me to create a DOC (because of course my code is written in Word2003), and yet I still cannot save. (I don't mind saving an Access MDB as a DOC on the grounds that I can always rename the extent later).

Question1: Am I looking at a bug with this RTE 748? That is, is MSAccess at fault, or is it me?
Question2: Is there a simpler way – that works – of opening and saving and closing an Access database from within Word?

Thanks
Chris
You do not have the required permissions to view the files attached to this post.
An expensive day out: Wallet and Grimace

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Word2003/MSAccess(2003) and "Using GetSaveAsFilename() in Access"

Post by HansV »

I don't see the need to open the database in Access. You can use the FileCopy instruction in VBA to copy any file.

GetSaveAsFileName is a method of the Excel.Application object; it's not in Word VBA nor in Access VBA.
You can specify the file type(s) presented by the dialog:

Code: Select all

    strFile = xlApp.GetSaveAsFilename(InitialFilename:="*.mdb", FileFilter:="Access Databases (*.mdb),*mdb")
Best wishes,
Hans

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Word2003/MSAccess(2003) and "Using GetSaveAsFilename() in Access"

Post by ChrisGreaves »

HansV wrote:
01 Oct 2020, 19:29
I don't see the need to open the database in Access. You can use the FileCopy instruction in VBA to copy any file.
Hans, my apologies. The macro presented here is stripped down to the bone; I don't open an Access file and then immediately save it with a new name. I open any MSOffice file, obtain the VBProject, and then do a barrel full of processing on the VBProject. After all, a VBProject is a VBProject is a VBProject, right?
The VBProject having been stripped/listed/nested/harvested etc etc, I hand the VBProject back to the User macro which neatly saves it, with a new name, as a file of the correct type.
I should have added a comment: "HUGE amount of processing here, then ..." between the Open and the saveAs.
GetSaveAsFileName is a method of the Excel.Application object; it's not in Word VBA nor in Access VBA.
Thanks too for this; you have caught me out in my laziness. I suspect that I wrote the User Macro interface code for Excel files and then cloned it once for each of the file types (Word, PPT, Access etc) without bothering to see if the cloned code was appropriate.
Back to the drawing board for me!
You can specify the file type(s) presented by the dialog:
And thanks for this. That tells me how old the code is. In my utility library (UW.dot), I now have compile-time constants and functions that deliver these masks. This code being so old must have been one of my first attempts.
Cheers
Chris
An expensive day out: Wallet and Grimace

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Word2003/MSAccess(2003) and "Using GetSaveAsFilename() in Access"

Post by HansV »

You could use FileCopy to create a copy of the database, then open the copy, edit it and close it.
Best wishes,
Hans

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Word2003/MSAccess(2003) and "Using GetSaveAsFilename() in Access"

Post by ChrisGreaves »

HansV wrote:
01 Oct 2020, 21:13
You could use FileCopy to create a copy of the database, then open the copy, edit it and close it.
Thanks Hans, I shall try this today.
Back to
Question1: Am I looking at a bug with this RTE 748? That is, is MSAccess at fault, or is it me?
Question2: Is there a simpler way – that works – of opening and saving and closing an Access database from within Word?

The answer to Question1 appears to be that this is not a bug; I am at fault for (a) sloppy code (using "xlApp" as an identifier and (b) thereby assuming that whatever works in one Office code will work in all the others. I have known for many a year that this is not so, and especially so with Access VBA.

The answer to Question2 is "yes - use Filecopy-and-Save instead of FilesaveAs", although the saveAs method, to my mind, looks neater in code (and lends itself to a version-numbering scheme for file names - Use <previous>+1).
Thanks
Chris
An expensive day out: Wallet and Grimace

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Word2003/MSAccess(2003) and "Using GetSaveAsFilename() in Access"

Post by HansV »

Question 1: The VBProject.SaveAs method does not work at all in Office VBA. It is a left-over from VBA's origin in Visual Basic 6.
Apart from that, xlApp.VBE.ActiveVBProject would refer to the active VBA project in Excel, i.e. the VBA project of a workbook, not to an Access database.

Question 2: That depends on what you want to do with the database. If you want to manipulate data, there is no need to open the database in Access. You can use DAO or ADODB to execute SQL statements and run stored queries. If, on the other hand, you want to run Access reports, for example, you would need to open the database in Access.
Best wishes,
Hans

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Word2003/MSAccess(2003) and "Using GetSaveAsFilename() in Access"

Post by ChrisGreaves »

HansV wrote:
02 Oct 2020, 10:25
Question 1: The VBProject.SaveAs method does not work at all in Office VBA. It is a left-over from VBA's origin in Visual Basic 6.
Hello Hans; I spent a bit of time considering your reply, and I think that in stripping down to the Open/saveAs code I oversimplified my goal. In particular by eliminating the objectives (11 different process to be applied to four different file types) I am guilty of misleading by providing insufficient data in the first place. Mea Culpa.


It DOES work, albeit I am going out on a limb using pre-97 technology.
The application has been treating Word/Excel/PPT files for 20 years (I believe) by opening the appropriate DOC/XLS/PPT file and then assigning an identifier to the ActiveVBProject. With the Word/Excel/PPT VBProject in hand, I can call a common procedure (in the example given "ProjectStripper") to strip the project. (I can also have a common procedure to List, to Nest, to Harvest etc any given vbProject).

The application has been quite happily opening doc/xls/ppt files, processing the VBproject, and then issuing a saveAs to allow the user to nominate a new name rather than overwriting an existing file.

I always felt that if I re-indented a project, or sorted its procedures in alphabetic order, that that process made a new version of the project and that it should be named with a new name, so a saveAs was the order of the day.

Two days ago when I tried to work on an Access2003 database, probably the first time ever, the saveAs collapsed.
That is what puzzled me.

I can accept (today) that Acess2003 does not support the saveAs whereas Word2003, Excel32003, Powerpoint2003 all do. Access always was different.
Apart from that, xlApp.VBE.ActiveVBProject would refer to the active VBA project in Excel, i.e. the VBA project of a workbook, not to an Access database.

Code: Select all

Public Sub StripAccessProject()
    Call LoadOptions(os)
    Call Initialize
    Dim strTemplate As String
    strTemplate = UW.Browsers.strBrowseFile2("", strcAccessProjectTypes, "T:")
    If Len(strTemplate) > 0 Then
        Dim xlApp As New Access.Application
        xlApp.OpenCurrentDatabase strTemplate
        If blnTestLockedProject(xlApp.VBE.ActiveVBProject) Then
            Dim strMsg As String
            strMsg = "The project " & xlApp.VBE.ActiveVBProject.FullName & " is locked."
            strMsg = strMsg & strcDelRec & strcDelRec & "Please unlock it and try again."
            MsgBox strMsg
        Else
            Call ProjectStripper(os, xlApp.VBE.ActiveVBProject)
        End If
        If xlApp.VBE.ActiveVBProject.Saved Then
            xlApp.VBE.ActiveVBProject.Close savechanges:=False
        Else
            Stop
            End
'            Dim fname As String
            Dim fname
            fname = xlApp.VBE.ActiveVBProject.GetSaveAsFilename
            fname = "V:\eraseme.mdb"
            xlApp.VBE.ActiveVBProject.SaveAs (fname)
        End If
        xlApp.Quit
        Set xlApp = Nothing
    Else ' User cancelled from the Browse
    End If
End Sub
I don't think so. My continued use of the identifier "xlApp" was bad coding on my part. We all see the "xl" prefix and think "Oh, he's raised an Excel Application", but no, I was being lazy, and once I got the thing working for an Excel vbProject, I pasted the code for each application and did NOT adjust the identifier, as I should have. So

Code: Select all

Dim xlApp As New Excel.Application
Dim docTemplate As Excel.Workbook
Set docTemplate = xlApp.Workbooks.Open(strTemplate)
(note that I first made my common code work within Word2003 on a Word2003 docTemplate and, again, did not change the identifier!)
and

Code: Select all

Dim xlApp As New PowerPoint.Application
Dim docTemplate As PowerPoint.Presentation
(Great! Now I am using Word-like and Excel-like identifiers on a PowerPoint object!)
and

Code: Select all

Dim xlApp As New Access.Application
xlApp.OpenCurrentDatabase strTemplate
Question 2: That depends on what you want to do with the database. If you want to manipulate data, there is no need to open the database in Access. You can use DAO or ADODB to execute SQL statements and run stored queries. If, on the other hand, you want to run Access reports, for example, you would need to open the database in Access.
In this case I do not want to manipulate the database (or the document, or the worksheets, or the slides) at all.
This application is interested only in the VBA program code in the VBProect. I am, in effect, talking to programmers work rather than to users work.
(Let me say that as I type this I am feeling ashamed that I ever taught VBA programming!)

I was clever in recognizing that a VBProject is a VBProject is a VBProject no matter where it arises.
I have wasted far too much time in being too lazy to make better use of identifiers.

Once I have split today's quota of logs, I will dive back into the code, and write it as I should have written it way back when.
I will probably use your FileCopy-then-save method to get the Access2003 vbProject working, and then turn back to the Access.saveAs at a later date.
I have burned up too much of your time!
Cheers
chris
An expensive day out: Wallet and Grimace

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Word2003/MSAccess(2003) and "Using GetSaveAsFilename() in Access"

Post by HansV »

Sorry, I overlooked the fact that xlApp is an Access.Application object, not an Excel.Application object.

If you want to export the code, I think you'll have to export each code module individually.
Best wishes,
Hans

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Word2003/MSAccess(2003) and "Using GetSaveAsFilename() in Access"

Post by ChrisGreaves »

HansV wrote:
02 Oct 2020, 11:48
Sorry, I overlooked the fact that xlApp is an Access.Application object, not an Excel.Application object.
Hans, you know how much I hate to argue with you :evilgrin: but I thought I had made it clear that it was my coding and posting error that did not make it clear that although it was called xlApp, it should really have been called mdbApp or similar.
If you want to export the code, I think you'll have to export each code module individually.
I already do that for those processes that need to export code. Harvest just loads the code to a searchable file, List just reads the code and extracts Procedure names, Indent just indents the code in place, etc.

Log cutting has stopped for the day. At 11am it is "too hot" for the chain saw guy!
Back to work for me.
Cheers
Chris
An expensive day out: Wallet and Grimace

LisaGreen
5StarLounger
Posts: 964
Joined: 08 Nov 2012, 17:54

Re: Word2003/MSAccess(2003) and "Using GetSaveAsFilename() in Access"

Post by LisaGreen »

Hi,

Coming to this late so apols if inappropriate.

I personally tend to use the filesystem object to read/write/copy/delete/move and so on. There are some nice methods that will return parts of a path string as well.

It's common across all applications and you can use it in VBS as well.

HTH
Lisa

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Word2003/MSAccess(2003) and "Using GetSaveAsFilename() in Access"

Post by ChrisGreaves »

ChrisGreaves wrote:
01 Oct 2020, 18:48
In a simple Word2003/VBA procedure I open a db1.mdb and immediately try to save it as db2.mdb. ...
And right there was my problem. As Hans has pointed out, Access is different from most other MSOffice applications; it has to be. Indeed a database-processor of any supplier has to be different from all other processors from that supplier. I have pondered this. I knew this over twenty years ago. Here is my explanation.

Word processors are good at creating readable renditions of ideas (Christmas letters and, sometimes, posts in online forums :evilgrin: )
Spreadsheet processors are good at effecting calculations on numbers.
Database processors are good at maintaining vast amounts of data. Compare the number of characters in last year's Christmas Letter with the volume required to store account and transaction details for all current and past clients of <your favourite national bank here>

The data in a database typically will exceed the RAM memory available to the database processor.

I knew this when I started getting paid to program. Data was updated in a tape-to-tape process. We read the input tape, matched its records against a deck of punched cards, and after amending the current record in RAM, we wrote the updated record to the output tape. That is, as each transaction was processed, we copied it away from RAM and out to the offline storage.

In Word we are urged to "save your work to disc frequently"
In Excel we are urged to "save your work to disc frequently"
In Access we are NOT urged to "save your work to disc frequently" because the work is done "on the disk; effectively, it is always in the status saved-to-disk.

In Access I modify a record in my Table and as soon as I move to the next record, the modified record is written back to disk. Just as I used to have to write the updated record to the output tape. There is no image of the database in RAM (as there is an image of the document or workbook in RAM, paging files notwithstanding).

For that reason there is, and there can be no concept of saveAs in Access.

The more I contemplate this the more naïve I seem to have been.

My sole defence in this matter is that in the application in question, "PROJE", I was not manipulating the data in a database, but was manipulating the VBA code in the VBProject of that database.

Nonetheless, Access is not the kind of beast that can contemplate "saveAs".

Cheers
Chris
An expensive day out: Wallet and Grimace