Modificar macro de copiar datos de word a excel
Voy a poner el código que tengo de un macro que me copia los datos de word a excel.
Quiero modificarlo ya que siempre me abre un cuadro para que elija que archivo es que voy a importar, quier que no me abra este cuando y siempre importe lo datos de "E:\Dropbox\Cuotas Vencidas.doc", es decir que lo haga automáticamente de ese archivo siempre.
Gracias de antemano
Option Explicit
Sub ImportWordTable()
Dim wdDoc As Object
Dim wdFileName As Variant
Dim tableNo As Integer 'table number in Word
Dim iRow As Long 'row index in Excel
Dim iCol As Integer 'column index in Excel
Dim resultRow As Long
Dim tableStart As Integer
Dim tableTot As Integer
On Error Resume Next
'wdFileName = Application.GetOpenFilename("Word files (*.doc),*.doc", , _
wdFileName = Application.GetOpenFilename("Word files (*.doc),*.doc", , _
"Browse for file containing table to be imported")
If wdFileName = False Then Exit Sub '(user cancelled import file browser)
ActiveSheet.Range("A:AZ").ClearContents
Set wdDoc = GetObject(wdFileName) 'open Word file
With wdDoc
tableNo = wdDoc.tables.Count
tableTot = wdDoc.tables.Count
If tableNo = 0 Then
MsgBox "This document contains no tables", _
vbExclamation, "Import Word Table"
ElseIf tableNo > 1 Then
tableNo = 1 'InputBox("This Word document contains " & tableNo & " tables." & vbCrLf & _
'"Enter the table to start from", "Import Word Table", "1")
End If
resultRow = 4
For tableStart = tableNo To tableTot
With .tables(tableStart)
'copy cell contents from Word table cells to Excel cells
For iRow = 1 To .Rows.Count
For iCol = 1 To .Columns.Count
Cells(resultRow, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)
Next iCol
resultRow = resultRow + 1
Next iRow
End With
resultRow = resultRow + 1
Next tableStart
End With
End Sub
1 respuesta
Respuesta de Abraham Valencia
1
[Hola
Pues si solo es eso que mencionas, basta un cambio bastante simple:
Sub ImportWordTable()
Dim wdDoc As Object
Dim wdFileName As Variant
Dim tableNo As Integer 'table number in Word
Dim iRow As Long 'row index in Excel
Dim iCol As Integer 'column index in Excel
Dim resultRow As Long
Dim tableStart As Integer
Dim tableTot As Integer
wdFileName = "E:\Dropbox\Cuotas Vencidas.doc"
ActiveSheet.Range("A:AZ").ClearContents
Set wdDoc = GetObject(wdFileName) 'open Word file
With wdDoc
tableNo = wdDoc.tables.Count
tableTot = wdDoc.tables.Count
If tableNo = 0 Then
MsgBox "This document contains no tables", _
vbExclamation, "Import Word Table"
ElseIf tableNo > 1 Then
tableNo = 1 'InputBox("This Word document contains " & tableNo & " tables." & vbCrLf & _
'"Enter the table to start from", "Import Word Table", "1")
End If
resultRow = 4
For tableStart = tableNo To tableTot
With .tables(tableStart)
'copy cell contents from Word table cells to Excel cells
For iRow = 1 To .Rows.Count
For iCol = 1 To .Columns.Count
Cells(resultRow, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)
Next iCol
resultRow = resultRow + 1
Next iRow
End With
resultRow = resultRow + 1
Next tableStart
End With
End Sub
Saludos]
Abraham Valencia
