VBA to pull various processes together

[buckwheat]

I have an imported excel spreadsheet in my Access database that needs to be updated each week with a fresh import. I also have an Update query that looks at the email field in the imported table and codes it to identify records without an email address. I then have a macro that exports the results to another specific excel spreadsheet.



Is there VBA that will run step 1 & 2 below at the same time:

IMPORT. . . REPORT . . . UPDATE
1) Replace existing records in the current imported table (Survey - DB) when importing the new weekly update?
2) Runs the Update query:

UPDATE [Survey - DB Link] SET Email = Null
WHERE instr([Email],"@")=0;

EXPORT
3) Instead of using a macro to export to a specific spreadsheet, VBA to export records and "add" a new tab to a specific spreadsheet. If I could have the option to name that tab before the export process, that would be great.

Many thanks

[June7]

Yes, a single button Click event could execute all code.

Why even need Excel component? What is method of import - can't just link to the Excel?

VBA can certainly do whatever the macro does, and more.

Here is one users solution for the export http://social.msdn.microsoft.com/For...3-a891fe1ae43b.

Alternative to TransferSpreadsheet would be opening the existing workbook as an Excel object in VBA that could be manipulated. I modified code from the following and it almost totally works. Unfortunately an instance of Excel persists in Task Manager processes. Something about the Add worksheet line that is not right.
http://social.msdn.microsoft.com/For...8-5617865b1e82
http://www.mrexcel.com/archive/VBA/1869.html
http://www.exceltip.com/st/Close_a_w...Excel/469.html

Solution to the Add sheet code found at http://www.vbforums.com/showthread.p...xisting-sheets
Can't get new sheet to add to end of sheets.

Will need VBA reference to Microsoft Excel 12.0 Object Library.

Private Sub SaveRecordsetToExcelRange()
  '  Excel Objects:
  Dim objXL As Excel.Application
  Dim objWBK As Excel.Workbook
  Dim objRS As DAO.Recordset
  '  Open a DAO recordset on the query:
  Set objRS = CurrentDb.OpenRecordset("SELECT * FROM table1;")
  '  Open Excel and point to the cell where the recordset is to be inserted:
  Set objXL = New Excel.Application
  objXL.Visible = True
  Set objWBK = objXL.Workbooks.Open("C:\Temp\Test.xlsx")
  objWBK.Worksheets.Add().Name = "Test"
  objWBK.Worksheets("Test").Range("A1").CopyFromRecordset objRS
  objWBK.Close True
  objXL.Quit
  '  Destroy objects:
  Set objWBK = Nothing
  Set objXL = Nothing
  If Not objRS Is Nothing Then
    objRS.Close
    Set objRS = Nothing
  End If
End Sub