Modules & VBA :: Save Workbook To Access Db?
May 6, 2014
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?
View Replies
ADVERTISEMENT
Oct 15, 2014
I am using the following line of excel vba code to extract the name of excel file that I would like to compare with Access table data.
Code:
strFile = Application.GetOpenFilename("Excel Files,*.xls*")
textbox1=strfile
Now i want to write VBA code to check sheet name "Summary" in that workbook and check if any of column D value matches with Access table tblmain field "Reference" values. If yes then change the status field of tblmain to "Yes".
View 1 Replies
View Related
Dec 5, 2013
I would like to open an Excel workbook from MS Access and clear cell contents, or just delete some records in a specific worksheet.
If you open the test workbook, cell contents in RAW need to be deleted by calling from Access.
I have produced some code but it's partially working.
Code:
Sub TestFileOpened()
' Test to see if the file is open.
If IsFileOpen("test.xls") Then
' Display a message stating the file in use.
MsgBox "File already in use!"
[Code] ....
If you put this in a standard module in access, the function works, but the part that doesn't work is where it says "activesheet". It somehow tries to recognize it as a variable, but it's not going to be a variable.
View 3 Replies
View Related
Apr 17, 2014
I have a question about the best way to go about linking an excel workbook to an ms access table? I have researched hyperlinks and attachments and ole objects and I am not sure what is the best option. I believe hyperlinks will be best, because the attachments will bloat the database, but I am not sure how to go about doing that in vba? My process is such:
1)Users enter project information
2)Users enter the sample information for each project
3)The project and sample information is combined by a query
4)The user clicks a button to print forms, which runs the query and opens an excel workbook template.
5)The data is exported to a specific sheet in the workbook
6)Using vba in excel, the data is used to populate cells in different sheets depending on critera.
After all of this is accomplished, I need the user to SAVE AS the excel workbook and it needs to be "attached" to the ms access 'project' table. I am not sure what is the best way to link this back to the database. I would like to automate it with vba if possible.
View 1 Replies
View Related
Jan 10, 2014
I have a few selected reports on an Access 2007 database that users can run. Is there a way for users to view the report, save as a PDF and automatically save a copy to a shared drive by modules/vba coding as an On Click event procedure?
View 4 Replies
View Related
Aug 7, 2014
I have a workbook which is already opened and I want to close it. the code that i have is (see below) is not working.
Code:
Function CloseExcel(sExcelFile As String) As Integer
Dim XLapp As New Excel.Application
Dim ObjXL As Excel.Workbook
On Error GoTo ErrorTrap
Set ObjXL = XLapp.Workbooks.Open(sExcelFile)
[Code] ....
View 4 Replies
View Related
Sep 24, 2014
I am currently trying to copy a tab from one workbook ("List.xls") to another ("IGEN_QC.xls") but I keep getting an error:
Method 'Copy' of object '_Worksheet' failed
Code:
Sub copydata()
Dim wkbSource As Workbook
Dim wkbDest As Workbook
Dim shttocopy As Worksheet
Dim wbname As String
On Error GoTo ErrHandl
[code]...
View 2 Replies
View Related
Jan 21, 2015
I put the formula in the Text box field in the control source in the property's tool box and i don't know if it will save that calculation to the table. I was wondering if i can do VBA code to save the the calculation in the table.I have a field called CA Weigt and i put the formula in there as =[Weight] * 0.4536 and it works. How would i put it in a vba code.
View 7 Replies
View Related
Nov 1, 2013
I am trying to write some code to open an existing excel form, populate it, and save it with a custom filename in a directory that is built using variables I have declared.
The only part I am having difficulty with is setting the pathname. Here is my code:
Code:
'Build path to save file to
spath = "S:ContractsGeomatics LABSigned" & Company
'Build File name
Filename = "" & reservationNumberforForm & " " & Company & "-Payment Schedule"
'Save the payment schedule to the S: drive
'xlApp.ActiveWorkbook.SaveAs Filename:="S:ContractsGeomatics LABSigned" & Filename, FileFormat:=xlOpenXMLWorkbook
xlApp.ActiveWorkbook.SaveAs Filename:="" & spath & " " & Filename, FileFormat:=xlOpenXMLWorkbook
xlApp.ActiveWorkbook.Saved = True
View 5 Replies
View Related
Dec 20, 2013
I have a excel object I have opened, and want to copy a query into a named range on the workbook.
heres the start have made
Sub openExcel()
'WillR - opens the specified Spreadsheet
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Set xlApp = New Excel.Application
With xlApp
[Code]...
View 2 Replies
View Related
Mar 20, 2014
I want to pass a string aPath contains path of the active workbook to access vba module or sub. Below is my excel vba code that will open access db form. How do i pass the string value to access vba.
Code:
Dim aPath, aDbase, aDSource, aTable, exePath As String
Dim fileParam As String
aPath = ActiveWorkbook.Path
aDbase = "near_14.accdb"
aDSource = aPath & "" & aDbase
[Code]...
View 4 Replies
View Related
Feb 12, 2014
So I have this relatively simple problem: I need to create a button that once clicked will open the Save As dialog box and allow the user to save a copy of the current database where he wishes. The filename should contain todays date in DDMM format along with some pre-set text e.g. DDMM PresetText.
I am using Access 2010.
View 2 Replies
View Related
Jun 21, 2015
If a File exists in CurrentProject.Path & "Book1.xlsx" , I want to delete it and create a new file . How can I do it using MsAccess vba .Also how to create a new Workbook Book1.xlsx with only one WorkSheet called Sheet1
View 3 Replies
View Related
Jul 26, 2012
I have an existing excel workbook that i would like to send via email using MS access macro through a windows scheduled task.
E.g. I want to send c: empWorkbook 1.xls through an "autoexec" macro from MS access. Is this possible?
View 1 Replies
View Related
Feb 3, 2013
I've build an Excel workbook that contains extensive use of VBA code for UserForms, Control Buttons, etc. and also contains several sheets that are formatted as Tables and function as databases. We've outgrown Excel's capabilities and need to move over to Access.
View 1 Replies
View Related
Jul 22, 2014
I have a procedure in Access that exports three reports to Excel, combines them in a single workbook, and then applies Excel formatting to some of the sheets. This procedure works the first time, but if I run it again it fails. In particular, I notice that things like "ActiveCell" and "Selection" will continue to refer to the previous workbook (dim xlWkb), even though I've activated the current worksheet/workbook.
Code:
Dim xlApp As Excel.Application
Dim xlWkb As Excel.Workbook
Dim xlBwkb As Excel.Workbook
[code]....
'BillingTemp, etc. are defined earlier in the procedure. I essentially am exporting Access reports to temporary Excel files, and then opening the files and putting them in a single workbook.
Set xlBwkb = xlApp.Workbooks.Open(BillingTemp)
xlBwkb.Sheets(1).Copy After:=xlWkb.Sheets(1)
xlBwkb.Close False
Set xlBwkb = xlApp.Workbooks.Open(PrismTemp)
xlBwkb.Sheets(1).Copy After:=xlWkb.Sheets(2)
xlBwkb.Close False
[code]....
'After combining the exported reports into one Excel workbook, I apply some formatting to some of the sheets.
Dim i As Integer
Dim rng As Range
Dim b As Range
Dim lastRowB As Integer
Dim Indication As String
Dim ws As Worksheet
[code]....
View 4 Replies
View Related
Aug 13, 2012
I'm importing data from Excel to Access using Access VBA which works fine that sheet name is first sheet in workbook. However, if multiple sheets exists in the workbook and that particular sheet can be 2 sheet, 3 sheet or any sheet. In that case how to search particular sheet name and import in access. Below is the code I have used for importing the data.
Code:
objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
sTmpTableName, sInput_Dir & "" & sInputFileName, True, sFile1Wks & "!"
View 2 Replies
View Related
Oct 15, 2014
I have an access query with around 10 columns. One of the columns is city. There are total of 5 unique cities. I need a macro for the button in the access report that will export the data from the access query to the ONE excel workbook in such a way that each city filtered data from access is exported to city name worksheet. So Excel file would have in total of 5 worksheets with the relevant city data.
View 2 Replies
View Related
Jul 16, 2013
I have three Queries and I need to export three queries into one workbook but different worksheet,
Currently I am using ExportWithFormatting , but the result came out is three different workbook .
Is there any way I can export to one workbook ?
View 3 Replies
View Related
Mar 14, 2014
I'm using the below to merge from acces to word and create a new doc. All is well.However I need to save the output file type as PDF.When I try to change the docx to PDF, it corrupts?
Dim wordApp1 As Word.Application
Dim docPath1 As String
Dim fileName1 As String
docPath1 = Application.CurrentProject.Path & "Merges"
fileName1 = docPath1 & "CreatedCover Letter.docx"
Set wordApp1 = New Word.Application
With wordApp1
[code]...
View 1 Replies
View Related
Jun 6, 2013
I have a table with lots of different fields and field types. The two memo fields will not save. I have tried Runcommand and if me.dirty. Neither seems to work.All fields are from one table and I have tried deleting the fields and repopulating them onto the form to reset defaults. The only way the two fields save is if I hit Ctrl-S.
None of the 100+ fields on this one form (yes, it is large, weird part is it fits Normalization upthrough BNSF) have the same problem. They all save fine with or without the extra code. I am using Windows 7 and Access 2010.
View 6 Replies
View Related
Feb 9, 2015
I need to save a new record from a form before it gets printed, the vba code behind the print button at the minute just opens the report and inserts the record in it. I need to put something in before that to save the record when the button is clicked.
The form in question is frmPlaceOrderFinal and I'm referring to the print button (command17). I've attached my system as well as an image for reference. The code that is currently behind the button:
Code:
Private Sub Command17_Click()
Dim strDocName As String
Dim strWhere As String
strDocName = "rptFinalInvoice"
strWhere = "[InvoiceID]=" & Me!InvoiceID
DoCmd.OpenReport strDocName, acPreview, , strWhere
End Sub
I need to save the record before it is opened in the report.
View 2 Replies
View Related
Nov 13, 2014
I have a form where the user can add as many rows as he wants - it's usually only 1 or 2 but that's not the point.When he has finished editing he clicks a Save and Exit button.In here I want to save data from the last row he has entered. I have this working but only if the cursor remains on the last row or on the 'new' record row. If he manages to, say, put his cursor on the penultimate row then I save the data for that.
eg.
Col1.........Col2..........Col3..............Col4
AA...........AAA...........AAAA.............AAAAA
BB...........BBB...........BBBB.............BBBBB
CC...........CCC...........CCCC.............CCCCC
DD...........DDD.........DDDD.............DDDDD
EE...........EEE............EEEE.............EEEEE
NEW LINE
So I for instance want to save Col3 on the last line filled in. Lets say the control is called Col3 then
If the cursor is on NEW LINE I have got the code so that
Me.Col3 "EEEE"
If the cursor is on the last line (the 5th) I have got the code so that
Me.Col3 "EEEE"
If the Cursor is on the first line i get "AAAA", "BBBB" for the 2nd etc. How do I always pick up the last line?
View 4 Replies
View Related
Jun 13, 2013
I currently have a working model which opens up a word document template, fills the bookmarks with info from access, saves the file as a preset name and then send the document on Outlook.
What I would like to do is save the document as a variable, preferably as something like " Climbing Request for site (and then to lift the site name from Access form field and insert here) "
E.G " Climbing Request - Newcastle Central Station.doc"
The location of the site name on access is...
Code:
objWord.Selection.Text = Forms![Front Page]![Site 2 Owner]
Currently I save the document using the below code...
Code:
objWord.ActiveDocument.SaveAs2 FileName:="C:UsersmedesktopFrom Colino2test.docx"
This is the last step before "launching" my database automation.
View 6 Replies
View Related
Feb 2, 2015
The code works only for tables and not query.
Code:
Dim dbs As DAO.Database
Dim rsQuery As DAO.Recordset
Set dbs = CurrentDb
Set rsQuery = dbs.OpenRecordset("myQuery")
Set rsImage = rsQuery.Fields("Images").Value
While Not rsImage.EOF
rsImage.Fields("FileData").SaveToFile "C:UsersmyNameDesktop"
rsImage.MoveNext
Wend
View 2 Replies
View Related
Jan 17, 2014
I'm using: DoCmd.OutputTo acOutputForm, "Frm_EL_PL_Bulk_Send", acFormatPDF, outputFileName1...To save a form as a PDF. The issue is, it saves all the records in that set in continuous form, and I'm trying to get it to only save the record its on.
View 3 Replies
View Related
