DaniWeb Forum Index > Software Development > Visual Basic 4 / 5 / 6

Visual Basic 4 / 5 / 6 RSS

copy excel file

Please support our Visual Basic 4 / 5 / 6 advertiser: Programming Forums - DaniWeb Sister Site

•

Join Date: Mar 2007

Posts: 1

Reputation: MyMel is an unknown quantity at this point

Solved Threads: 0

MyMel

Offline

Newbie Poster

copy excel file

May 1st, 2007

Hi everybody,

Help PLEASE!!

I'm sure it something simple but I can't seem get my mind around
it right now.

I am exporting data from a current access form to an excel
spreadsheet then I am copying that data to an existing excel
workbook where the data will be used as the backend for and
excel invoice form. (I am using 2 workbooks because I can't
figure out how to output the data to the specific workbook and
sheet that I will use so I am trying to copy it there but I
can't get that to work either. Would someone please review what
I have and give me some guidance, please.

Private Sub EMail_Click()

Dim xLS As Object
Dim TempWbk As Object
Dim Sheet As String
Dim DestSheet As String

Set xLS = CreateObject("Excel.Application")

With xLS
.Workbooks.Open ("C:\temp\granite\Global Financial Adjustment
Form.XLS")
.Application.Visible = True
.Sheets("Sheet2").Select
End With

DoCmd.OutputTo acOutputForm, "frmGlobalAdjustment", _
acFormatXLS, "C:\temp\granite\Holding Form.XLS", True
Sheet = ActiveWorkbook
DestSheet = ("C:\temp\granite\Global Financial Adjustment
Form.XLS")

ActiveWorkbooks(Sheet).Sheets(frmGlobalAdjustment).Move_
after = Workbooks("Destsheet").Sheets(1)

End Sub

Thank you,

Melody

•

Join Date: Feb 2007

Posts: 114

Reputation: davidcairns is an unknown quantity at this point

Solved Threads: 8

davidcairns davidcairns is offline

Offline

Junior Poster

Re: copy excel file

May 2nd, 2007

Try this

 Help with Code Tags 
 vb Syntax (Toggle Plain Text) 
Private Sub EMail_Click()
 
    Dim objXL As Excel.Application
    Dim objWB As Excel.Workbook
 
    DoCmd.OutputTo acOutputForm, "frmGlobalAdjustment", _
    acFormatXLS, "C:\temp\granite\Global Financial Adjustment Form.XLS", True
 
    Set objXL = New Excel.Application
    Set objWB = objXL.Workbooks.Open("C:\temp\granite\Global Financial Adjustment Form.XLS")
    objXL.Visible = True
 
    Set objWB = Nothing
    Set objXL = Nothing
 
End Sub
Private Sub EMail_Click()

    Dim objXL As Excel.Application
    Dim objWB As Excel.Workbook

    DoCmd.OutputTo acOutputForm, "frmGlobalAdjustment", _
    acFormatXLS, "C:\temp\granite\Global Financial Adjustment Form.XLS", True

    Set objXL = New Excel.Application
    Set objWB = objXL.Workbooks.Open("C:\temp\granite\Global Financial Adjustment Form.XLS")
    objXL.Visible = True

    Set objWB = Nothing
    Set objXL = Nothing

End Sub