Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old March 15th, 2004, 12:14 AM
Registered User
 
Join Date: Mar 2004
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Opening an excel file with VBA

Whats up everyone I am new to VBA and I am trying to write a macro that will open an excel file, copy a selection and paste to another workbook. Any help would be great. I cant figure out how to get the file open. Thanks in advance for any help.
Reply With Quote
  #2 (permalink)  
Old March 15th, 2004, 03:43 AM
joefawcett's Avatar
Wrox Author
Points: 9,716, Level: 42
Points: 9,716, Level: 42 Points: 9,716, Level: 42 Points: 9,716, Level: 42
Activity: 9%
Activity: 9% Activity: 9% Activity: 9%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,067
Thanks: 1
Thanked 37 Times in 36 Posts
Default

You don't say what application you are working with. If it's Word then you'll need to add a reference to Excel from the tools | references menu in the code editor then your code would be like:
Code:
Dim oExcel as Excel.Application
Dim oWB as Workbook
Set oExcel = new Excel.Application
Set oWB = oExcel.Workbooks.Open(<pathToWorkbookHere>)
'Rest of code
--

Joe
Reply With Quote
  #3 (permalink)  
Old March 17th, 2004, 11:37 PM
Registered User
 
Join Date: Mar 2004
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Im sorry for the confusion, I am writing a macro to use in excel. What I would like the macro to do is this:

Open an excel sheet (Test.xls) Copy cells F4:F67 and paste into the results sheet which is where the macro will be stored. Any help would be great. If you need any more info let me know.

Reply With Quote
  #4 (permalink)  
Old March 19th, 2004, 01:52 PM
Registered User
 
Join Date: Mar 2004
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Can Anybody help me?

Reply With Quote
  #5 (permalink)  
Old March 21st, 2004, 08:05 AM
Friend of Wrox
 
Join Date: Oct 2003
Location: , , Finland.
Posts: 168
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

I'm not sure if you mean this kind of operation but anyway
this code may help you

Sub CopyCells()
    Workbooks.Open Filename:="C:\test.xls"
    Range("F4:F67").Copy
    ThisWorkbook.Activate
    Sheets("Result").Activate
    '*** Select the destination cell
    Range("F4").Select
    ActiveSheet.Paste
    Workbooks("Test.xls").Close
End Sub

-vemaju



Reply With Quote
  #6 (permalink)  
Old May 20th, 2004, 12:13 PM
Registered User
 
Join Date: May 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

With this code can open a file Excel from VB



Public Sub ObtenerExcel(xArch As String)
Err.Clear
On Error GoTo xErrGetExcel

   Dim MiXL As Object ' Variable que contiene la referencia
                        ' a Microsoft Excel.
   Dim ExcelNoSeEjecutaba As Boolean ' Indicador para liberación final .
   Dim xNotepad

    Set MiXL = CreateObject("Excel.Application")

   Set MiXL = GetObject(, "Excel.Application")

   If Err.Number <> 0 Then ExcelNoSeEjecutaba = True
   Err.Clear ' Borra el objeto Err si se produce un error.

   DetectarExcel

   Set MiXL = GetObject(xArch)

   MiXL.Application.Visible = True
   MiXL.Parent.Windows(1).Visible = True

   If ExcelNoSeEjecutaba = True Then
      MiXL.Application.Quit
   End If

   Set MiXL = Nothing ' Libera la referencia a la
                        ' aplicación y a la hoja de cálculo.

Exit Sub
xErrGetExcel:
    If Err.Number = 429 Then
        MsgBox "Usted no tiene instalado EXCEL en su máquina." & Chr(13) & _
               "El archivo " & UCase(xArch) & _
               " se mostrará con un editor de texto.", vbExclamation, App.Title
        xNotepad = Shell("Notepad" & " " & UCase(xArch), vbNormalFocus)
        Exit Sub
    End If
    MsgBox "Se presentó un error en la subida de Excel" & Chr(13) & _
            Err.Number & " = " & Err.Description, vbCritical, App.Title
    Exit Sub

End Sub


Public Sub DetectarExcel()
Err.Clear
On Error GoTo xErrDetectExcel

   Const WM_USER = 1024
   Dim hWnd As Long

   hWnd = FindWindow("XLMAIN", 0)
   If hWnd = 0 Then ' 0 quiere decir que Excel no se está ejecutando .
      Exit Sub
   Else
      SendMessage hWnd, WM_USER + 18, 0, 0
   End If

Exit Sub
xErrDetectExcel:
     MsgBox "Se presentó un error en la detección de Excel" & Chr(13) & _
            Err.Number & " = " & Err.Description, vbCritical, App.Title
     Exit Sub

End Sub



Reply With Quote
  #7 (permalink)  
Old May 23rd, 2014, 12:08 PM
Registered User
Points: 3, Level: 1
Points: 3, Level: 1 Points: 3, Level: 1 Points: 3, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2014
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to get the path of current excel sheet

If I am writing VBA code in the same excel file

then how I have to use the path parameters and access the specific sheet from the Excel book
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem in opening excel file in MS Excel 2000 kallol Visual C++ 0 November 16th, 2007 04:48 AM
Opening an Excel file in OWC10.AxSpreadSheet ashu_from_india Visual Studio 2005 0 May 31st, 2007 06:32 AM
Opening an Excel file (via Atom feed) hadenough Excel VBA 0 April 30th, 2007 01:18 PM
Opening Excel file p2pMember ASP.NET 1.0 and 1.1 Professional 1 January 21st, 2006 12:54 PM



All times are GMT -4. The time now is 04:30 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.