?
Solved

MS Access VBA to copy Excel formats from one worksheet to another

Posted on 2011-04-26
7
Medium Priority
?
501 Views
Last Modified: 2012-05-11
Hello,
I am trying to copy formats from one worksheet to another but with the below code, I receive a "Script out of range" error message - run time error 9.

Private Sub TestExport_Click()
DoCmd.TransferSpreadsheet acExport, , "QfltDataTotals", "H:\Email\Master-template.xlsx", False, "Finance"
Workbooks("H:\Email\Master-template.xlsx").Sheets("Finance").Range("A1:C2000").Copy Destination:= _
Workbooks("H:\Email\Master-template.xlsx").Sheets("Finance1").Range("A1:C2000").PasteSpecial(xlPasteFormats)
MsgBox "The Spreadsheet has been populated!", vbOKOnly
End Sub
0
Comment
Question by:CFMI
  • 4
  • 3
7 Comments
 
LVL 7

Expert Comment

by:manthanein
ID: 35472063
Dim objExcel As Object

    Set objExcel = CreateObject("EXCEL.APPLICATION")
    DoCmd.TransferSpreadsheet acExport, , "QfltDataTotals", H:\Email\Master-template.xlsx", False, "Finance"
    With objExcel
      .Visible = True
      .Workbooks.Open "H:\Email\Master-template.xlsx"
      .Sheets("Finance").Copy Before:=.Sheets(1)
      .activesheet.Name = "Finance1"
    End With
    Set objExcel = Nothing
   
0
 
LVL 1

Author Comment

by:CFMI
ID: 35474760
Hello Expert,
Your code is so close as it creates an additional worksheet with the correct formats but without the data.  I attached a spreadsheet to help display the accomplishment and how close to completion you are.  We begin with a Workbook with two Worksheets (FinanceTemp & Tax).  The FinanceTemp worksheet is just the template with the correct formats.  As the code runs, it enters the data into “Finance” then “Finance (1)” gets created which is identical to the FinanceTemp worksheet.
I renamed the original worksheet from Finance to FinanceTemp since an error message stopped the processing as I also updated the below code, however I am lost on how to move the data values from one worksheet to another.  Can you please help?
Private Sub TestExport_Click()
Dim objExcel As Object
    Set objExcel = CreateObject("EXCEL.APPLICATION")
    DoCmd.TransferSpreadsheet acExport, , "QfltDataTotals", "H:\Email\Master-template.xlsx", True, "Finance"
    With objExcel
      .Visible = True
      .Workbooks.Open "H:\Email\Master-template.xlsx"
      .Sheets("FinanceTemp").Copy Before:=.Sheets(1)
      .ActiveSheet.Name = "Finance1"
    End With
    Set objExcel = Nothing
MsgBox "The Spreadsheet has been populated!", vbOKOnly
End Sub
 Master-template.xlsx
0
 
LVL 7

Accepted Solution

by:
manthanein earned 2000 total points
ID: 35480642
you could try this
this export the query to a new workbook  
opens the template file, clears  the finance-temp table
copy data from exported file to template
close the exported file

BTW, you should convert your  excel  to  Table  to automate formatting.   it can be found on the Insert menu.

Private Sub TestExport_Click()
Dim objExcel As Object
    Set objExcel = CreateObject("EXCEL.APPLICATION")
    DoCmd.TransferSpreadsheet acExport, , "QfltDataTotals", "H:\Email\finance.xlsx", True, "Finance"
    With objExcel
      .Visible = True
      .Workbooks.Open "H:\Email\Master-template.xlsx"
      .Sheets("FinanceTemp").select 
      .Range("B2").Select
      .Range(.Selection, .ActiveCell.SpecialCells(11)).Select
      .Selection.Delete Shift:=-4159 
      .Workbooks.Open "H:\Email\finance.xlsx"
      .Sheets("Finance").select 
      .Range("B2").Select
      .Range(.Selection, .ActiveCell.SpecialCells(11)).Select
      .Selection.copy
      .workbooks("Master-template.xlsx").activate 
      .Activesheet.paste
      .workbooks("finance.xlsx").close false 
    End With
    Set objExcel = Nothing
MsgBox "The Spreadsheet has been populated!", vbOKOnly
End Sub

Open in new window

0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 1

Author Comment

by:CFMI
ID: 35486228
Wow, your code is so close as it brings the data into the template but it overwrites the formats already there.  Is there VBA code to Paste Special Values?  Also, just before completion, a message appears stating there is a lot of information on the Clipboard.  Do you want to paste this information into another program later?  I am hoping there is VBA code to stop this message from appearing.

FYI...I am trying to automate this export for an Accountant that wants the spreadsheet already formatted.  I really appreciate your help as I also enjoyed your message about converting to Table format.
0
 
LVL 1

Author Comment

by:CFMI
ID: 35490545
Thank you Export for helping me to export using the correct field formats and transferring the file into a particular worksheet however I still receive a message stating there is a lot of information on the Clipboard.  Do you want to paste this information into another program later?  Is there VBA code to stop this message from appearing?  The below code extracts using field formats into a temporary file and copies and pastes the file into a specific worksheet then closes the files and deletes the temporary file.
Dim objExcel As Object
    Set objExcel = CreateObject("EXCEL.APPLICATION")
    DoCmd.OutputTo acOutputQuery, "QfltDataTotals", acFormatXLS, "H:\Email\finance.xls", False, ""
    With objExcel
      .Visible = True
      .Workbooks.Open "H:\Email\Master-template.xlsx"
      .Sheets("FinanceTemp").Select
      .Range("A1").Select
      .Range(.Selection, .ActiveCell.SpecialCells(11)).Select
      .Selection.Delete Shift:=-4159
      .Workbooks.Open "H:\Email\finance.xls"
      .Sheets("QfltDataTotals").Select
      .Range("A1").Select
      .Range(.Selection, .ActiveCell.SpecialCells(11)).Select
      .Selection.Copy
      .Workbooks("Master-template.xlsx").Activate
      .ActiveSheet.Paste
      .Workbooks("finance.xls").Close False
      .Workbooks("Master-template.xlsx").Close False
    End With
    Set objExcel = Nothing
    Kill "H:\Email\finance.xls"
MsgBox "The Spreadsheet has been populated!", vbOKOnly
0
 
LVL 1

Author Comment

by:CFMI
ID: 35492116
Hello Experts,
Thanks for your help!  I added the below statement and the message does not appear:
objExcel.DisplayAlerts = False

Thank you very much...
0
 
LVL 7

Expert Comment

by:manthanein
ID: 35509469
sorry  for not  responding..  I wwas on leave  for  5 days..  and I don't  have any  internet  connections ..
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question