Excel Automation

  • Thread starter Thread starter Brennan
  • Start date Start date
B

Brennan

I am using the code below to automate the transfer of information from Access
to Excel. Right now, the code creates a file, modifies it and saves it. I
would to change the code so that it prompts the user for the file into which
data is transfered and then keeps it open for review. How do I change the
code to accomplish this? Thanks for your help!

Dim xlobject As Object, xlsheet As Object

Set xlobject = CreateObject("excel.sheet.5")
Set xlsheet = xlobject.Application.activeworkbook.sheets("sheet1")

With xlsheet
.range("b1").Value = "Presale"
.range("c1").Value = "Postsale"
.range("d1").Value = "Total P&L"
.range("a2").Value = "Consulting Revenue"
.range("b2").Value = PreCR
.range("c2").Value = PostCR
.range("d2").Value = CR
.Cells(1, 1).Font.Size = 25

End With

xlsheet.Parent.SaveAs "C:\Documents and
Settings\bs185075\Desktop\xltest.xls"
xlobject.Application.Quit
Set xlobject = Nothing


Brennan
 
I am using the code below to automate the transfer of information from Access
to Excel.  Right now, the code creates a file, modifies it and saves it..  I
would to change the code so that it prompts the user for the file into which
data is transfered and then keeps it open for review.  How do I change the
code to accomplish this?  Thanks for your help!

 Dim xlobject As Object, xlsheet As Object

   Set xlobject = CreateObject("excel.sheet.5")
   Set xlsheet = xlobject.Application.activeworkbook.sheets("sheet1")

   With xlsheet
       .range("b1").Value = "Presale"
        .range("c1").Value = "Postsale"
         .range("d1").Value = "Total P&L"
      .range("a2").Value = "Consulting Revenue"
      .range("b2").Value = PreCR
      .range("c2").Value = PostCR
      .range("d2").Value = CR
      .Cells(1, 1).Font.Size = 25

   End With

    xlsheet.Parent.SaveAs "C:\Documents and
Settings\bs185075\Desktop\xltest.xls"
    xlobject.Application.Quit
   Set xlobject = Nothing

Brennan
Click to expand...

use the OpenSaveFile API from Access Web. Prompt the user for the
filename, then store it in a variable. Then you can save the file
using that variable's stored value.
 
Thanks for the tip. I went to the site that you recommended and followed the
instructions. It seemed to work, but I got the following error. Could you
take a look and let me know what I am doing incorrectly? Thanks again for
the help.

Run Time Error '91':
Object variable or with block variable not set

Here is my current code:
Private Sub Command87_Click()
Dim xlobject As Object, xlsheet As Object
Dim strFilter As String
Dim strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)


Set xlobject = GetObject(strInputFileName)
Set xlsheet = xlobject.Application.activeworkbook.sheet1
With xlsheet
.range("b1").Value = "Presale"
.range("c1").Value = "Postsale"
.range("d1").Value = "Total P&L"
.range("a2").Value = "Consulting Revenue"
.range("b2").Value = PreCR
.range("c2").Value = PostCR
.range("d2").Value = CR
.range("a3").Value = "Customer Education"
.range("b3").Value = PreCE
.range("c3").Value = PostCE
.range("d3").Value = CE
.range("a4").Value = "Total Revenue"
.range("b4").Value = PreTR
.range("c4").Value = PostTR
.range("d4").Value = TR
.Cells(1, 1).Font.Size = 25


End With

Set xlobject = Nothing


End Sub
 
Back
Top