Opening an excel file with VBA

TwEaKFrEaK · March 15th, 2004, 01:14 AM

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.

joefawcett · March 15th, 2004, 04:43 AM

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

TwEaKFrEaK · March 18th, 2004, 12:37 AM

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.

TwEaKFrEaK · March 19th, 2004, 02:52 PM

Can Anybody help me?

vemaju · March 21st, 2004, 09:05 AM

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

Marcela · May 20th, 2004, 12:13 PM

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

hasan · May 23rd, 2014, 12:08 PM

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