Can I save workbook to access db?

mkaeser

Registered User.
Local time
Today, 05:41
Joined
Apr 14, 2014
Messages
74
Hi, I have some vba code that exports the results of a query to a specific excel workbook. Code in this workbook then takes the exported data to "fill out" areas in the workbook. My staff will then use this workbook to perform analysis on a project. After they have completed the analysis, they will then save the excel workbook, and another person will then validate this excel and turn it into a PDF to send as an email attachment.

My question is, when my staff saves the excel workbook, is there a way they can save it directly to the access database? If not, would there be a way to use vba to "attach" this excel workbook to the appropriate record in the access db for future use? Thank you!
 
It not completely clear what you want to do.
Do you want to update existing records with new information?
Do you want to import the data to a new table?
Do you want to attach the excel file to an existing record?

All these things can be done and can be done with different approaches.

For example - code can be added to the excel file to connect to access to push the data.
Code could be added to access to pull data from excel.

You need to better define you requirements - including which user will be invoking the process and from which system will the process reside.

Additionally, its not such a good idea to start adding lots of attachments to an access db if other methods of storage and retrieval are available. The attachments if large and many will bloat the database and can negatively effect performance. You would be better suited by establishing a secure location on the network for storing the file and updating your access record with a link to the file.

Refine your requirements and re-ask your questions when you have hit a wall.
Hope this helps
Fran
 
So I guess the best way to explain it without exhaustive details is I need to export data to excel then attach that workbook back to the access record it was exported from....hope that makes sense...
vba code in access behind a button on a form automates the export of the record to a specific excel template. I need a way to automate the save for the excel workbook back to the access record the data came from...
 
I am not sure if linking will work for what I need to do. I do not need to use the data in the excel file. The data that is exported form the access record into the excel template is used as a template for analysis purposes. The record in access contains project details that will be exported to the excel template. This workbook will then be printed and used to collect raw data. I need to "SAVE AS" this excel workbook with the project as the file name, and I would like to somehow automate where to "SAVE AS" this file. I would like for there to be a way to click a button in access to open this workbook with the project details, as a hyperlink more likely so the database doesn't bloat from attachments. So to sum up, after the export to excel, I need to "SAVE AS" that workbook with the project name as the file name, then have a hyperlink (or whatever other method is superior) automatically created and added to the record back in excel. Can this be done? Thank you
 
mkaeser

All is doable with some minor changes to you systems and processes.

1. You will need to modify your Project table to include a field to store the Excel File Location and name - Basically a full path

2. Add VBA code or macro to the click event of this field calling the Application.FollowHyperlink Me.NewFieldName - This will be used to open the excel file after its been referenced

3. Designate a shared network location for excel files to be stored. Either everyone who should have access to this share should have it shared with the same drive letter or use the UNC nomenclature when updating access.

4. Make sure the data that is initially exported to excel includes the Primary Key and Project Code/Name of the Project

5. Add VBA code to the Template Excel file used in the export process (Your template will need to be a macro enabled template if its not already.

See example of some of the code below you can use to update the Access db.

This code does not take care of naming the Excel File with the Project name nor does it include any save as functionality to save the Excel file.
It assumes the file has already been saved.

Additionally it does not use UNC but rather whatever Excel returns as the File name which could include a drive letter.

I'm not at all familiar with the Excel object model so someone else should help you with these aspects of your project.


This code assumes you have added a command Button to the worksheet to initialize the process.
Code:
Public Const myAccessFile As String = "Path to your Access DB Goes Here"
Public Const myADOProvider As String = "Microsoft.ACE.OLEDB.12.0"

Sub Button1_Click()
    Dim mySavedExcelFileName As String
    
    mySavedExcelFileName = ActiveWorkbook.FullName
    
    UpdateFileLocaton mySavedExcelFileName
    
End Sub

Public Sub UpdateFileLocaton(MyFileLoc As String)

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Dim ProjectID As Long
Dim mySql As String

'Get ProjectID from ActiveWorksheet -
'Change Range to Cell that has the key to record you want to update
    Range("D2").Activate
    ProjectID = ActiveCell.Value


'Define Sql to be used to retrieve your project to update
'Replace myTestProj with your table name
    
    mySql = "Select * from myTestProj Where ID = " & ProjectID

' connect to the Access database
    Set cn = New ADODB.Connection
    With cn
        .Provider = myADOProvider
        .Properties("Data Source").Value = myAccessFile
        .Open
    End With

' open a recordset
Set rs = New ADODB.Recordset
    
    With rs
        .ActiveConnection = cn
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open mySql
        .MoveFirst
        
    'Change Field name to name of field you will be using to contain link to excel file
        !ExcelFileLocation = MyFileLoc
        
        .Update
        
    End With

    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

End Sub


One thing to consider which has not been discussed is security and archiving.
-- Will everyone who has access to the Project Record in Access be able to open the file?
-- Should the file be saved as read-only with a write protection password?

Hope this helps. :)
Fran
 
Thank you so much for that detailed explanation! I will look it over and report back!
 

Users who are viewing this thread

Back
Top Bottom