• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 533
  • Last Modified:

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

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
CFMI
Asked:
CFMI
  • 4
  • 3
1 Solution
 
manthaneinCommented:
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
 
CFMIFinancial Systems AnalystAuthor Commented:
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
 
manthaneinCommented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
CFMIFinancial Systems AnalystAuthor Commented:
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
 
CFMIFinancial Systems AnalystAuthor Commented:
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
 
CFMIFinancial Systems AnalystAuthor Commented:
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
 
manthaneinCommented:
sorry  for not  responding..  I wwas on leave  for  5 days..  and I don't  have any  internet  connections ..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now