Automate XML Export
Jul 11, 2006
Hi:
I want to automate the XML export function of Access and not sure if it is possible or how to do it. In going through the menu bar (File ->Export) interface, I must choose the root table, and then subsequent tabbed windows allow one to select what tables to be exported, if I want the schema to be generated, the target filename, etc.
Instead of having to go through this large selection process, I'd like to write a script that I can run consistently over the same tables, produce the same file name, etc. Can anybody provide a suggestion/code/pointer on how to accomplish this? Thanks in advance.
John
View Replies
ADVERTISEMENT
Aug 8, 2006
Hi all, I'm using access 2002. 2002 has pivoting, but in order to use it you have to download an xp add on(at least I did). After I installed the libraries pivots worked for me- and I added all manner of niftyness-before I discovered that no one else in my department could use it without adding the same downloads. So I decided to go with exporting to excel. That opened up a whole new can of worms, but in the end I finally got it working. I just wanted to share what I found with everyone here-maybe the next person won't have to work so hard. This code works from a toolbar button.
Of course if anyone has any suggestions, I'm interested.
Public Function goToPivot()
'automates creating a formatted pivotChart in excel from a query in access2002
'because of some trickiness with objects, no 'with's are used
On Error GoTo Err_goToPivot
Dim xlApp As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlPT As Excel.PivotTable
Dim DataRange As String
Dim ExcelFile As String
Dim queryPivot As String
'set relative path and filename of new spreadsheet
ExcelFile = Application.CurrentProject.Path & "xPivot.xls"
queryPivot = "querypivotChartTest"
' Delete file if it exists
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(ExcelFile) Then
' Delete if not read only
fso.DeleteFile ExcelFile, False
End If
'export query to excel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
queryPivot, ExcelFile, True
Set xlApp = New Excel.Application
xlApp.Visible = True
Set XlBook = xlApp.Workbooks.Open(ExcelFile)
'set style and range of cells, name pivotTable
DataRange = queryPivot & "!" & XlBook.Worksheets(queryPivot).UsedRange. _
Address(ReferenceStyle:=xlR1C1)
Set XlPT = XlBook.PivotCaches.Add(xlDatabase, DataRange).CreatePivotTable( _
TableDestination:="", TableName:="Pivot_Table1", _
DefaultVersion:=xlPivotTableVersion10)
'create pivotChart, preload it with fields to get user started
XlBook.Charts.Add
XlBook.ActiveChart.Location xlLocationAsNewSheet, "RCA pivot"
XlBook.ActiveChart.PivotLayout.PivotTable.AddDataF ield XlBook.ActiveChart.PivotLayout. _
PivotTable.PivotFields("SIRs"), "Count of SIRs", xlCount
XlBook.ActiveChart.PivotLayout.PivotTable.PivotFie lds("Team").Orientation = xlRowField
XlBook.ActiveChart.PivotLayout.PivotTable.PivotFie lds("Team").Position = 1
'set axes and chart titles, size and fonts of pivotChart
XlBook.ActiveChart.HasTitle = True
XlBook.ActiveChart.ChartTitle.Characters.Text = "RCA DATA ANALYSIS"
XlBook.ActiveChart.ChartTitle.Font.Bold = True
XlBook.ActiveChart.ChartTitle.Font.Size = 18
XlBook.ActiveChart.Axes(xlCategory, xlPrimary).HasTitle = True
XlBook.ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "CATEGORY"
XlBook.ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
XlBook.ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "TOTAL"
XlBook.ActiveChart.SizeWithWindow = True
'optional-create, save, then close
'XlBook.Saved = True
'xlApp.Quit
'Set XlPT = Nothing
'Set XlBook = Nothing
'Set xlApp = Nothing
Exit_goToPivot:
Exit Function
Err_goToPivot:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_goToPivot
End Function
View 1 Replies
View Related
Jul 28, 2006
Hi,
Would it be possible for me to build an interface for a customer to use for importing data. So that they customer could choose from a drop down list, or input into a text box where they want to export from, and where they would like the export to be imported?
View 1 Replies
View Related
Jun 29, 2013
I've created an outlook task to export a common query I do every day, is there a way to automate it so it will happen automatically without me pressing Run?
I use office 2007 on a company laptop with tight network restrictions (so no Windows Task Scheduler)..
View 1 Replies
View Related
May 10, 2007
Hi guys,
I regularly export the main table in our database to an excel spreadsheet, to provide an additional level of data back-up. (Using File/Export)
I was wondering if there is a way of automating this process, either through a command button on a form, or by linking it with shutting the database down.
Any ideas?
View 1 Replies
View Related
May 10, 2007
Hello,
Does anyone knows how can I export the export/import specifications (which file/directory) and how can I import/export the specifications between different versions of Access. Thanks!
View 3 Replies
View Related
Sep 20, 2006
I have a database of chemicals and one of the entries is the MSDS number. I would like a hyperlink, pointing to the .pdf of the chemical, created when I enter the MSDS number. If the MSDS number is 1111 the .pdf file will be something like (\servernamefolder1111.pdf). Is there a way to store \serverfolder in a string, append the MSDS number, and .pdf then store that in a hyperlink field. Any suggestions?
View 3 Replies
View Related
Jun 27, 2007
I am trying to automate a report to run at night. I have created a batch file that will run a macro each night. But, when Access starts and loads my database I get a Security Warning [ This file may not be safe if it contains code that was intended to harm your computer]
I have tried to use a Sendkeys command to send an 'O' to go ahead and open the database but it doesnt work.
Also, my report goes to a pdf converter and it asks for a file name. I just want the macro to accept the default and continue.
How do I do this?
Thanks in advance
KD
View 1 Replies
View Related
Nov 8, 2005
I was working on project that involved writing the data into Access database using a C program. The insert query execution was taking very long, so I decided to write to a .csv file and then import it to one of the desired tables. This worked very fast as compared to directly writing to the DB. Now I want to automate this process. The user should specify the file name at the command prompt and I want to call a script so that the script automatically imports the contents of the .csv file into the access DB. I already have connection established to the DB. All I am looking for is the script that can automatically import the .csv file into access DB. Please help me out. :confused:
View 2 Replies
View Related
Jan 25, 2006
I have read more post than i care to think about but I can't stil get my head round the following problem.
I have created a database (with this forums help) but I am stuck on the following query/form/vba etc....
Our staff holidays run from 01/05/05 until 30/04/06 and i have a query that calculates what holiday entitlement staff are due from 01/05/05 on a daily basis up to 23 days worth of hols ending on 30/04/06, but I can't work out how to set next years holiday period 01/05/06 - 30/04/07 without changing the form manually, is there a way for it to auto update??
View 2 Replies
View Related
Aug 10, 2006
I want to be able to automaticallly send a mouse click in response to a message using a Macro or VBA code. Is this possible, Any suggestions appreciated
thanks
View 2 Replies
View Related
Sep 16, 2006
Anybody got a working example / sample of faxing through Winfax10
Wish list
1.Use the FaxNo. On form
2.Send a Report
Looked through the threads but no conclusion reached
Cheers in advance
View 1 Replies
View Related
Nov 8, 2006
Can anyone please advise how I may be able to achieve the following?
The database in question is used to store info about members of a radio control model flying club.
I have a table (members) containing member’s personal details – names, addresses etc and a second table (club_membership) containing membership info in the Fields – year, membership_cat, insurance_cat. Each member has a record in the second table for each year they have been a member.
To simplify the renewal process (typically most members renew at the same time at our AGM) I would like to use a form which lists all members names with a check box (renew) which is checked if the member renews his/her membership. For every member checked I would like to automatically create a new record in the club_membership table for the next membership year (2007) by copying over values for the fields membership_cat and insurance_cat from their record for the previous year.
Many thanks
Russell
View 1 Replies
View Related
Dec 29, 2004
Is it possible to automate a update query to run whenever a table has a new record added? If so, how is it done?
Thank you.
View 2 Replies
View Related
Aug 24, 2006
Hi all,
Access 2000
Wondering if there is anyway to setup a report to automatically run and e-mail out at a certain time each day? I currently have a button on a form, based on a macro, that when clicked, will e-mail the report to a certain user. Just wondering if there is anyway to set it to send at a certain time, without having to open the database and manually run it.
Thanks in advance.
J
View 1 Replies
View Related
Oct 11, 2006
Hi,
I have created a mail merge from a query in Access and it works fine if you run it from word but I would like to know how to automate the mail merge from Access using the Command button. Please can someone advise.
Thanks
Alex
View 5 Replies
View Related
Jan 31, 2008
I have a reasonable large dbase with some 12 tables and some 40 Queries.
In three of the tables, there are fields with dubious names like AA1, CD-3, 227, etc.
I want to change these field names to some more appropriate names like : Testblok , Cspeler, Jaarnummer, etc, etc.
In doing so, I will have to go thru each and every Query ( and at a later stage forms/reports) to change the fieldnames in that query corresponding to the changes I made in the initial table field name.
Would there be another way of doing this, i.e. is there a routine for changes made in the fieldnames, being “automatically updated in the table bound query.
View 6 Replies
View Related
Jun 12, 2006
Automate the run of a parameter query
I have a parameter query in ACCESS that generates an ACCESS report for 250 facilities. (Each facility has their own ID of course.) Every other month I need to run this report on all 250 different facilities. Then I give each facility their report by dropping it into an electronic folder.
This is a lot of work. Is there a way that I can automate the generation of these reports? Perhaps somehow pass the ID numbers?
I need each report to continue to be generated as a separate file.
View 7 Replies
View Related
Apr 8, 2015
I have access database as FE and teradata and peoplesoft as BE. In access database, i have a table with 5 columns and around 50,000 rows of data. I need to create the pass through queries such that it selects the first row from the access table as a where clause and export the query results in excel format or access table. The query should then go to the 2nd row as a next where clause and append the results in the same excel sheet or table.This process need to continue for all rows in the access table.
View 14 Replies
View Related
Jun 25, 2013
I use a macro (SendObject), which works, but it requires Us, or someone to go into Outlook to click on send.I'm new to vba, do I have to code something on outlook to send automatically?
View 6 Replies
View Related
Mar 6, 2013
I have encountered PC Lockdown when I tried this onClick Code. I am trying to post a data from MSAccess field into a specific cell in Excel. The Excel File is formatted already as document. It is submitted for Attendance records. But I couldn't find the problem and fix to automate it properly.
Code:
Private Sub cmdRequery_Click()
On Error GoTo Err_AttSum
Dim xlApp As Excel.Application
[code]...
View 9 Replies
View Related
Nov 1, 2012
I have a database for when our people are out in the field. The people will fill out large questionnaires that I want to import into our main database. How can I automate this?
View 10 Replies
View Related
Sep 7, 2013
I've been asked importing about 50 - 60 .DBF files into an Access database daily - and processing the data in Access to specs I don't have yet.
I'm pretty comfortable doing whatever once I have the data in the Access Database.
View 8 Replies
View Related
Feb 7, 2005
Hey guys,
I have spent a while surfing around the site for the answer to this problem but to no avail.
I have a form with a command button which when pressed opens a Word Document. It's a mail merge template and I want to be able to automate the actual merge (merge to new a new Word document) as soon as the Word document opens.
Any help with this would be greatly appreciated. I am using the code below and cannot get the objWord.MailMerge.Execute line to work.
Rusty
:D
Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
'Dim objWord As Word.Document
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMultiSelect")
' Loop through the selected items in the list box and build a text string
For Each varItem In Me!lstTrials.ItemsSelected
strCriteria = strCriteria & ",'" & Me!lstTrials.ItemData(varItem) & "'"
Next varItem
' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not select any Trials from the list." _
, vbExclamation, "Nothing to find"
Exit Sub
End If
' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
' Build the new SQL statement incorporating the string
strSQL = "SELECT * FROM Trials " & _
"WHERE Trials.[Name of Trial] IN(" & strCriteria & ");"
' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the MailMerge template
Call Shell("""C:Program FilesMicrosoft OfficeOfficeWINWORD.EXE"" ""O:ASWCSRecruitment dataCTU Cancer Trial Monthly UpdatesMonthlyUpdateTemplate.doc""", 1)
'objWord.MailMerge.Execute
' Empty the memory
Set db = Nothing
Set qdf = Nothing
Exit_cmdOK_Click:
Exit Sub
Err_cmdOK_Click:
Resume Exit_cmdOK_Click
End Sub
View 11 Replies
View Related
Jan 3, 2014
I have a large database of items we sell on Amazon, I am looking to automate the process of uploading the inventory.
I am uploading a tab delimeted text file using the following code,
With CreateObject("msxml2.xmlhttp")
.Open "POST", strURL, False
.setRequestHeader "Host:", "mws.amazonservices.co.uk"
.setRequestHeader "User-Agent:", "VBA"
.setRequestHeader "Content-MD5:", md5hdr2
.setRequestHeader "Content-Type:", "text"
.send c2a
Forms!Form1.Text3.value = .responseText
End With
I am confident I have the signature and the MD5 Header, but I cannot get the data into Amazon!
I keep getting a non-descript error "InputDataError".
When debugging my Play API, I was told that the "send" command was not uploading the file contents, it was uploading the filename! So c2a is a string variable that contains the tab delimited data. This works like a charm for Play, but no joy for Amazon.
View 4 Replies
View Related
Mar 12, 2014
I run a report based on dates I enter (see below)
>=[Date you wish to view DD/MM/YY] And <=[Enter the Date after DD/MM/YY]
I would like to automate this if possible, the dates are enter are always a set pattern
If its Monday I enter the previous Thursday and Sunday
If Its Tuesday I enter the previous Sunday and Monday
If Its Wednesday I enter the previous Monday and Tuesday
If Its Thursday I enter the previous Tuesday and Wednesday
If Its Friday I enter the previous Wednesday and Thursday
I dont run rthe report on weekends
View 7 Replies
View Related
