Export data on FORM in text box to Excel 1

[MrsTFB]

I've been READING about exporting Access records to Excel. All I need are 6 fields which will be housed on a form to be moved to a particular sheet in Excel. I took a training class and was able to accomplish this, of course now...it's not working.

Here's what I'm using, any ideas?

Private Sub Command31_Click()
On Error GoTo Err_Command31_Click


Dim X As New Excel.Application

X.Visible = True
X.WORKBOOKS.Open ("Q:\CODE\QUOTE2.XLS")
' X.Worksheets.Select ("Sign-Off Record")


CUSTOMER.SetFocus
X.Range("E5").Activate
ActiveCell.Value = CUSTOMER.Text


DESCRIPTION.SetFocus
X.Range("E8").Activate
ActiveCell.Value = DESCRIPTION.Value

DATEORIG.SetFocus
X.Range("D12").Activate
ActiveCell.Value = DATEORIG.Value

DATETARGET.SetFocus
X.Range("E7").Activate
ActiveCell.Value = DATETARGET.Value

SALES1.SetFocus
X.Range("E12").Activate
ActiveCell.Value = SALES1.Value

ActiveWorkbook.SaveAs ("TEST.XLS")
ActiveWorkbook.Close

X.Quit

Exit_Command31_Click:
Exit Sub

Err_Command31_Click:
MsgBox Err.DESCRIPTION
Resume Exit_Command31_Click

Command31.SetFocus


End Sub

Thanks in advance,
MrsTFB

 

[SkipVought]

Hi,

Range has a WORKSHEET as object

' X.Worksheets.Select ("Sign-Off Record")
 
 
 CUSTOMER.SetFocus
 X.Worksheets("Sign-Off Record").Range("E5").Value = CUSTOMER.Text

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[MrsTFB]

Thanks, Exactly what I needed!

MrsTFB

 

[MrsTFB]

MORE Please...I made those changes. Now I'm trying to save the workbook as the RFQ Number with the customer name. Here's the code I have:
I'm getting the error: Method 'Range' of object'_Global' failed on the Save As line. I've tried a few things, but nothing is working. I know this is not that hard, but my knowledge is limited.

THanks for any help,
MrsTFB
Dim answer
Dim X As New Excel.Application

X.Visible = True
X.Workbooks.Open ("Q:\CODE\QUOTE2.XLS"), updatelinks:=3

CUSTOMER.SetFocus
X.Worksheets("Sign-Off Record").Range("E5").Value = CUSTOMER.Text


DESCRIPTION.SetFocus
X.Worksheets("Sign-Off Record").Range("E8").Value = DESCRIPTION.Text


DATEORIG.SetFocus
X.Worksheets("Sign-Off Record").Range("D12").Value = DATEORIG.Text


DATETARGET.SetFocus
X.Worksheets("Sign-Off Record").Range("E7").Value = DATETARGET.Text


SALES1.SetFocus
X.Worksheets("Sign-Off Record").Range("E12").Value = SALES1.Text


X.ActiveWorkbook.SaveAs ("Q:\QUOTES\" + Range("e6").Value + Range("E5").Value + ".XLS")
X.ActiveWorkbook.Close

X.Quit

 

[Nikita6003]

MrsTFB,

be specific when you refer to a range object - i.e. use the X.Worksheets("name") object in front of your range

HTH

Cheers
Nikki
Look, mommy, I'm flying!

 

[MrsTFB]

LIKE I JUST HAD TO DO TO TRANSFER THE INFORMATION.....DUMMY ME!
I can't believe it was the SAME mistake....thanks for opening my eyes!

Mrstfb

 

[SkipVought]

   Dim answer
   Dim X As New Excel.Application
 
   With X
      .Visible = True
      .Workbooks.Open ("Q:\CODE\QUOTE2.XLS"), UpdateLinks:=3
      With ActiveWorkbook
      
         With .Worksheets("Sign-Off Record")
            .Range("E5").Value = CUSTOMER.Text
   
            .Range("E8").Value = Description.Text
   
            .Range("D12").Value = DATEORIG.Text
   
            .Range("E7").Value = DATETARGET.Text
   
            .Range("E12").Value = SALES1.Text
            
            ActiveWorkbook.SaveAs ("Q:\QUOTES\" + .Range("e6").Value + .Range("E5").Value + ".XLS")
      
         End With
         .Close
      End With
      .Quit
   End With

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PHV]

I would change this:
With ActiveWorkbook
By this:
With .ActiveWorkbook
And this:
ActiveWorkbook.SaveAs
By this:
X.ActiveWorkbook.SaveAs
Just to avoid hidden instantiation of implicit objects.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244