Access - exportar tablas de access a excel

   
Vista:

exportar tablas de access a excel

Publicado por Cesar (7 intervenciones) el 06/06/2012 07:17:40
Hola buen dia, disculpen, tengo una duda, ojalá me pudiesen ayudar:
consiste en que tengo una tablas que he crado en acces y deseo exportarlas en un mismo archivo de excel y que enga la fecha actual, he intentado con el siguiente codigo pero aun no me queda, el codigo es el siguiente:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
Private Sub Comando13_Click()
 
Dim nomExcel As String
 
 
Dim miRange1 As String
Dim miRange2 As String
Dim miRange3 As String
Dim miRange4 As String
Dim miRange5 As String
Dim miRange6 As String
Dim miRange7 As String
Dim miRange8 As String
Dim Fecha As String
Fecha = Format(Date, "medium date")
 
 
nomExcel = "\C:\Documents and Settings\eq0469\My Documents\Daniel Velasco\MRP\MRP" & Fecha & ".xls"
 
miRange1 = "primer nivel"
miRange2 = "Segundo nivel"
miRange3 = "tercer nivel"
miRange4 = "Cuarto nivel"
miRange5 = "Quinto nivel"
miRange6 = "Sexto nivel"
miRange7 = "Septimo nivel"
miRange8 = "ResumenMRP"
 
 
 
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "primer nivel", nomExcel, , miRange1
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Segundo nivel", nomExcel, , miRange2
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "tercer nivel", nomExcel, , miRange3
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Cuarto nivel", nomExcel, , miRange4
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Quinto nivel", nomExcel, , miRange5
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Sexto nivel", nomExcel, , miRange6
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Septimo nivel", nomExcel, , miRange7
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "ResumenMRP", nomExcel, , miRange8
 
 
 
MsgBox "Exportación Realizada", vbInformation, "Finalizar"
 
 
 
End Sub

ojala me pudiesen ayudar.
Saludos
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder

exportar tablas de access a excel

Publicado por jose (741 intervenciones) el 06/06/2012 08:31:44
rem relaciones con excell
Rem pasa datos a excell


Dim objExcel As Object
Dim libro As Object

Const xlHairline = 1
Const xlMedium = -4138
Const xlThick = 4
Const xlThin = 2
Const xlWBATWorksheet = -4167

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set libro = objExcel.Workbooks.Add

' creo un libro con una sola hoja
'libro.Workbooks.Add xlWBATWorksheet
strHoja = libro.ActiveSheet.Name
'libro.ActiveSheet.Name = "jose"



Rem ----cabecera
libro.ActiveSheet.Cells(1, 1) = "LISTADO DE TELEFONOS NOMBRE EMPRESA S.L.U "
libro.ActiveSheet.Cells(2, 1) = "AUXILIARES FECHA " & Now()



REM ENCABEZADO DE COLUMNAS EN LA FILA 4

libro.ActiveSheet.Cells(4, 1) = RS.Fields(0).Name : REM QUI PUEDE PONER CUALQUIER NOMBRE DE COLUMNA
libro.ActiveSheet.Cells(4, 2) = RS.Fields(1).Name
libro.ActiveSheet.Cells(4, 3) = RS.Fields(2).Name
libro.ActiveSheet.Cells(4, 4) = RS.Fields(3).Name
libro.ActiveSheet.Cells(4, 5) = RS.Fields(4).Name
'libro.ActiveSheet.Cells(1, 6) = RS.Fields(5).Name
'libro.ActiveSheet.Cells(1, 7) = RS.Fields(6).Name
'libro.ActiveSheet.Cells(1, 8) = RS.Fields(7).Name
'libro.ActiveSheet.Cells(1, 9) = RS.Fields(8).Name
'libro.ActiveSheet.Cells(1, 10) = RS.Fields(9).Name

'libro.ActiveSheet.Cells(1, 11) = RS.Fields(10).Name
'libro.ActiveSheet.Cells(1, 12) = RS.Fields(11).Name
'libro.ActiveSheet.Cells(1, 13) = RS.Fields(12).Name
'libro.ActiveSheet.Cells(1, 14) = RS.Fields(13).Name
'libro.ActiveSheet.Cells(1, 15) = RS.Fields(14).Name
'libro.ActiveSheet.Cells(1, 16) = RS.Fields(15).Name
'libro.ActiveSheet.Cells(1, 17) = RS.Fields(16).Name
'libro.ActiveSheet.Cells(1, 18) = RS.Fields(17).Name
'libro.ActiveSheet.Cells(1, 19) = RS.Fields(18).Name
'libro.ActiveSheet.Cells(1, 20) = RS.Fields(19).Name

'libro.ActiveSheet.Cells(1, 21) = RS.Fields(20).Name
'libro.ActiveSheet.Cells(1, 22) = RS.Fields(21).Name
'libro.ActiveSheet.Cells(1, 23) = RS.Fields(22).Name
'libro.ActiveSheet.Cells(1, 24) = RS.Fields(23).Name
'libro.ActiveSheet.Cells(1, 25) = RS.Fields(24).Name
'libro.ActiveSheet.Cells(1, 26) = RS.Fields(25).Name

'libro.ActiveSheet.Cells(1, 27) = RS.Fields(26).Name

'libro.ActiveSheet.Cells(1, 28) = Rs.Fields(27).Name
'libro.ActiveSheet.Cells(1, 29) = Rs.Fields(28).Name
'libro.ActiveSheet.Cells(1, 30) = Rs.Fields(29).Name




REM A PARTIR DE LA LINA 5 ESCRIBIMOS LOS DATOS QUE ESTAN EN ESTE CASO EN UN RECORDSET, PERO QUE PUEDES OBTENERLOS DE DONDE QUIERAS

n = 5

Do While RS.EOF = False
'MsgBox Rs.Fields(4).Value
' escribo los datos en la hoja
libro.ActiveSheet.Cells(n, 1) = RS.Fields(0).Value
libro.ActiveSheet.Cells(n, 2) = RS.Fields(1).Value
libro.ActiveSheet.Cells(n, 3) = RS.Fields(2).Value
libro.ActiveSheet.Cells(n, 4) = RS.Fields(3).Value
libro.ActiveSheet.Cells(n, 5) = RS.Fields(4).Value
' libro.ActiveSheet.Cells(n, 6) = RS.Fields(5).Value
' libro.ActiveSheet.Cells(n, 7) = RS.Fields(6).Value
' libro.ActiveSheet.Cells(n, 8) = RS.Fields(7).Value
' libro.ActiveSheet.Cells(n, 9) = RS.Fields(8).Value
' libro.ActiveSheet.Cells(n, 10) = RS.Fields(9).Value

' libro.ActiveSheet.Cells(n, 11) = RS.Fields(10).Value
' libro.ActiveSheet.Cells(n, 12) = RS.Fields(11).Value
' libro.ActiveSheet.Cells(n, 13) = RS.Fields(12).Value
' libro.ActiveSheet.Cells(n, 14) = RS.Fields(13).Value
' libro.ActiveSheet.Cells(n, 15) = RS.Fields(14).Value
' libro.ActiveSheet.Cells(n, 16) = RS.Fields(15).Value
' libro.ActiveSheet.Cells(n, 17) = RS.Fields(16).Value
' libro.ActiveSheet.Cells(n, 18) = RS.Fields(17).Value
' libro.ActiveSheet.Cells(n, 19) = RS.Fields(18).Value
' libro.ActiveSheet.Cells(n, 20) = RS.Fields(19).Value

' libro.ActiveSheet.Cells(n, 21) = RS.Fields(20).Value
' libro.ActiveSheet.Cells(n, 22) = RS.Fields(21).Value
' libro.ActiveSheet.Cells(n, 23) = RS.Fields(22).Value
' libro.ActiveSheet.Cells(n, 24) = RS.Fields(23).Value
' libro.ActiveSheet.Cells(n, 25) = RS.Fields(24).Value
' libro.ActiveSheet.Cells(n, 26) = RS.Fields(25).Value
' libro.ActiveSheet.Cells(n, 27) = RS.Fields(26).Value

' libro.ActiveSheet.Cells(n, 28) = Rs.Fields(27).Value
'libro.ActiveSheet.Cells(n, 29) = Rs.Fields(28).Value
'libro.ActiveSheet.Cells(n, 30) = Rs.Fields(29).Value



'libro.ActiveSheet.Cells(n, 1).CopyFromRecordset Rs
'xls.ActiveSheet.Cells(2, 1).CopyFromRecordset rstPedidos





RS.MoveNext
n = n + 1

Loop
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar