Exporting data to a new xls workbook

[Frank10]

Hi,

I am trying to export data from an xlsm to a new xls.

But the problem is that it only copies the first row and I need a table to be copied.


My macro is this one:

Sub ExportarTXT()

Worksheets("Import").Activate
If Sheets("Import").Activate Then
Range("A1").Select
If ActiveCell = Empty Then GoTo salte
Open "c:\ZLibra.xls" For Output As 1
regresa:
Codigo = ActiveCell
Print #1 , Codigo
ActiveCell.Offset(1, 0).Select
If ActiveCell = Empty Then GoTo salte
GoTo regresa:

salte:
Close #1
End If
Worksheets("Import").Activate

End Sub


Thanks,

 

[6StringJazzer]

The code that you have here is writing the data to an ASCII text file, even though you are naming it .xls. This won't work.

Please describe the data you want to export. You called it a table, but the code is just writing data from column A. Can you give a more complete description of where/what the data is?

 

[Frank10]

Hi,

Thanks for the answer. This macro exports ok the first column.
I need to extend it till column "O".

The data is like this.

[TABLE="width: 1654"]
<colgroup><col><col><col><col><col><col span="2"><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]ASIENTO[/TD]
[TD]FECHA[/TD]
[TD]CUENTA[/TD]
[TD]CONCEPTO[/TD]
[TD]MONEDA[/TD]
[TD] DEBE [/TD]
[TD] HABER [/TD]
[TD]TIPO[/TD]
[TD]COTIZACION[/TD]
[TD]CENTRO[/TD]
[TD]REFERENCIA[/TD]
[TD]LOCAL[/TD]
[TD]RUT[/TD]
[TD]TRIBUTO[/TD]
[TD]ORIGEN[/TD]
[/TR]
[TR]
[TD]154[/TD]
[TD]2/1/2017[/TD]
[TD]1155119[/TD]
[TD]Pago BPS[/TD]
[TD]2[/TD]
[TD] 994.93[/TD]
[TD] - [/TD]
[TD]W[/TD]
[TD]-2.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD]ZetaLIBRA[/TD]
[/TR]
[TR]
[TD]154[/TD]
[TD]2/1/2017[/TD]
[TD]1112101[/TD]
[TD]Pago BPS[/TD]
[TD]2[/TD]
[TD] - [/TD]
[TD] 994.93[/TD]
[TD]W[/TD]
[TD]-2.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD]ZetaLIBRA[/TD]
[/TR]
[TR]
[TD]155[/TD]
[TD]2/1/2017[/TD]
[TD]1155119[/TD]
[TD]Pago BPS[/TD]
[TD]1[/TD]
[TD] 646,789.00[/TD]
[TD] - [/TD]
[TD]W[/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD]ZetaLIBRA[/TD]
[/TR]
</tbody>[/TABLE]

 

[6StringJazzer]

This assumes that you want to export the entire worksheet.

Sub ExportarTXT()


   Dim ExportWB As Workbook
   
   Set ExportWB = Workbooks.Add
   ThisWorkbook.Worksheets("Import").Cells.Copy ExportWB.Sheets(1).Range("A1")
   ExportWB.SaveAs FileName:="c:\ZLibra.xls", FileFormat:=xlExcel8
   
End Sub

 

[Frank10]

Stringjazzer,

Thanks a lot for the help. Sorry for not fully explain myself.

I need to export to that workbook in values not in formulas.
Also there are formulas at the end that show all the row in blank. So if I go Ctrl+shift+down it goes all down till that blank rows also.
I need to export only the ones that have values but not blank.

Sorry for the inconvenience.

Thanks again.

 

[6StringJazzer]

This takes the values instead of formulas. In such a case blank cells become a non-issue.

Sub ExportarTXT()




   Dim ExportWB As Workbook
   
   Set ExportWB = Workbooks.Add
   ThisWorkbook.Worksheets("Import").Cells.Copy
   ExportWB.Sheets(1).Range("A1").PasteSpecial xlPasteValues
   ExportWB.SaveAs Filename:="c:\ZLibra.xls", FileFormat:=xlExcel8
   
End Sub

 

[Frank10]

Thak you 6StringJazzer!

 

[Frank10]

6StringJazzer,

Sorry one more thing.

What can I add to this macro for after exporting to the ZLibra.xls refresh cells from A1:P2000. For the date to apply format and blank cells being removed.

Thankss!! a lot

 

[Frank10]

This macro but then copying A1:P2000 and pasting like values, then refresh A1:P2000 cells.


Sub ExportarTXT()


Dim ExportWB As Workbook

Set ExportWB = Workbooks.Add
ThisWorkbook.Worksheets("Import").Cells.Copy ExportWB.Sheets(1).Range("A1")
ExportWB.SaveAs FileName:="c:\ZLibra.xls", FileFormat:=xlExcel8

End Sub



Im so bad explaining myslef. This is the last oneee

 

[6StringJazzer]

"What can I add to this macro for after exporting to the ZLibra.xls refresh cells from A1:P2000. For the date to apply format and blank cells being removed."

I'm sorry but I have no idea what you mean by this.

The code you posted in post 9 is not the last version I provided. You should use the code in post 6.