0

I am trying to read files *.xls from u:\test folder. There is one sheet in every file. I want to copy cell B1 and to paste it into new file to A1. Then I want to copy range B1:B57 and to paste it into the new file/sheet to B1:B57. Then I want to copy range K1:U57 and copy (the values only) to the new file/sheet C1 location. I am doing this in Visual Basic 6 and I have problem to find out how to use the range function... I tried to activated and select the sheet(1). Then I wanted to use the command Set SrchRange = ActiveSheet.Range(Cells(2, 1)). I got error 1004 Application defined or object defined error. Here on this line. If I would successed to select/copy/paste the cell areas to new file/sheet, then I would like to save the current file as txt, given the .txt extenssion. How to correct this code to reach the goal?

Sub FromExcelToNpad()
    'export activesheet as txt file
    Dim my_files As String
    Dim folder_path As String
    Dim wb As Workbook, NewWB As Workbook
    Dim ws As Worksheet
    Dim SrcRange As Range
    folder_path = "u:\test"
    my_files = Dir(folder_path & "\*.xls", vbDirectory)
    Do While my_files <> vbNullString
       Set wb = Workbooks.Open(folder_path & "\" & my_files)
       Set ws = wb.Sheets(1)
       Set NewWB = Workbooks.Add
       ws.Activate
       ws.Select
       Set SrchRange = ActiveSheet.Range(Cells(2, 1))
       wb.ActiveSheet.UsedRange.Copy NewWB.Sheets(1).Range("A1")
       wb.Close True
       Application.DisplayAlerts = True
       my_files = Dir()
    Loop
   
End Sub

Update The range and cells are copied:

Dim my_files As String
Dim folder_path As String
Dim wb As Workbook, NewWB As Workbook
Dim ws As Worksheet
folder_path = "u:\test"
my_files = Dir(folder_path & "\*.xls", vbDirectory)
Do While my_files <> vbNullString
   Set wb = Workbooks.Open(folder_path & "\" & my_files)
   Set ws = wb.Sheets(1)
   Set NewWB = Workbooks.Add
   ws.Range("B1").Copy NewWB.Sheets(1).Range("A1")
   ws.Range("B3:B57").Copy NewWB.Sheets(1).Range("A3:A57")
   ws.Range("K1:U57").Copy
   NewWB.Sheets(1).Range("B1:L57").PasteSpecial xlValues
   wb.Close True
   With NewWB
        Application.DisplayAlerts = False
        .SaveAs Filename:=folder_path & "\" & my_files, FileFormat:=xlText
        .Close True
        Application.DisplayAlerts = True
   End With
   wb.Save
   my_files = Dir()
Loop

I am trying to save the file as .txt . I have error Run time error - automation error. Also there is a dialog asking me if I want to save data from a "page". How to turn this off?

John Boe
  • 3,501
  • 10
  • 37
  • 71
  • You are doing this in `Visual Basic 6` or `VBA`? That's not clear and those are 2 totally different approaches. Please clarify which one it is. – Pᴇʜ Jul 06 '21 at 12:36
  • 1
    Change `ActiveSheet.Range(Cells(2, 1))` to `ActiveSheet.Cells(2, 1)` • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Jul 06 '21 at 12:36
  • Visual Basic. I mean, the language of Visual Basic is VBA? – John Boe Jul 06 '21 at 12:46
  • @Peh: Thank you. It passed. But the copy command does not copy the selected area to the destination. `Set SrchRange = ActiveSheet.Cells(2, 1) SrchRange.Copy NewWB.Sheets(1).Range("A1")` – John Boe Jul 06 '21 at 12:56
  • 1
    Well in your text you say you want to copy Cell B1 To A1. So do that `ws.Range("B1").Copy NewWb.Sheets(1).Range("A1")` Do the same with the other ranges you want to copy. • You can remove the `ws.Activate` and `ws.Select` lines they are not needed. – Pᴇʜ Jul 06 '21 at 13:01
  • @Peh. May you please check the updated code and help me to save the file? – John Boe Jul 06 '21 at 13:32
  • 1
    Check the content of `my_files` it is probably ending as `.xls` and needs to be changed to `.txt` • Since you did not change anything in `wb` you don't need to save it on closing. Change `wb.Close True` to `wb.Close False`. And remove `wb.Save` in the end. You cannot save a workbook that you already closed before. – Pᴇʜ Jul 06 '21 at 14:02
  • @Peh: I replaced it `my_files = Replace(my_files, ".xls", ".txt")` but still there is the runtime error. Yet there is empty file `1.txt` has been created on disk. – John Boe Jul 06 '21 at 14:10
  • In which line do you get the error? In `.SaveAs`? Did you change both `.Close True` to `.Close False` and remove the `wb.Save`? – Pᴇʜ Jul 06 '21 at 14:13
  • @Peh: Yes, the error is on the line with .Save() . Now updated not to save the temporal file. – John Boe Jul 06 '21 at 14:25
  • 1
    In this line `wb.Save`? As I told you should remove it. You cannot save `wb` because you alreay closed it with `wb.Close`. – Pᴇʜ Jul 06 '21 at 14:27
  • Ah. Ok. So now I have removed it and tested it. I have found out that the file 1.xls has been overwritten with no data. So thïs is why the 1.txt has been empty. Thank you. It works now. – John Boe Jul 06 '21 at 14:31

0 Answers0