0

I have a issue with copy/paste the data from one file to another after i changed the range of rows to be transferred. Until now i used a file with 147 rows and 6 columns, no i have to use a file with 579 rows in 6 columns. But when i try to transfer the data with the 549 rows i got the message : "There is already data here. Do you want to replaced it". It is very annoying and i don't know what's happening, while the data is in the same format, only the rows are much more. I think the Separate part is the problem, but not sure. Here's the part of the code, making that issue :

Sub OpenF()
Dim Window As String
Dim sPath As String
Dim VarName As String
Dim GetBook As String
Dim sFile As String
VarName = Sheet1.Range("a4").Value
Window = VarName & "FileName_3_15min.csv"
    ChDir sPath & "\Schedule"
   ' Open the fail
   sFile = sPath & "\Schedule\" & Window
   If Dir(sFile) = "" Then
   MsgBox "File: (" & Window & ") is missing"
   Else
     Workbooks.Open Filename:= _
    sPath & "\Schedule\" & Window
    Cells.Select
    Selection.Copy
     Windows(GetBook).Activate
     Sheets("Sheet.5").Select
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 'Separate
Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :=";", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        1), Array(6, 1)), TrailingMinusNumbers:=True
        Sheets("WindFarmBalchik_2.1").Columns("A:G").AutoFit
' Close the fail

    Windows(Window).Activate
    Application.CutCopyMode = False
    ActiveWindow.Close SaveChanges:=False
End if
End Sub
Harati
  • 1
  • 3
  • Most likely there are data on the sheet. But you defiinitly should read [How to avoid select](https://stackoverflow.com/q/10714251/16578424) ... there are a lot of problems that you can avoid by following the tips in that thread. – Ike Sep 29 '22 at 14:33
  • Yeah, i am sure there is some data that is causing the problem, but still can't figure it out how to avoid or fix this issue in this very case. Thank you for pointing the topic - it will very helpful for my next project. – Harati Sep 30 '22 at 06:03
  • Actually i cant understand why when there is 147 rows in the data file, after the copy and Text-to-Column action there is no problem, but when the rows are 579 the issue is there and shows me the "There is already data here. Do you want to replaced it" message. After all the format of the data files is the same, just the rows are more (in the 147 file is 1hour resolution; in the 579 rows file is 15min resolution). – Harati Sep 30 '22 at 07:42
  • But what about the target sheet - there could be data, let's say from row 500 onwards - which is not problem with the short source file but with the longer source file – Ike Sep 30 '22 at 07:49
  • I think i found the problem - in the new 579r file there last column name have a "," : "Forecast(AvgPower,kW)" (just before kW), and this comma some how makes the problem. So how can i fix this with the comma sign inside the last columns name? – Harati Sep 30 '22 at 07:57
  • There are several options: ask the department that creates the file to remove the comma or remove it in your VBA code – Ike Sep 30 '22 at 08:18
  • Can you help me what piece of code i have to put in, so only the comma is removed? Or how to replace the AvgPower,kW with AvgPower kW in this particular cell? – Harati Sep 30 '22 at 09:10
  • Thank you, Ike for the idea! I was able to manage the issue with a simple Replace code, as you suggested for the AvgPower,kW with AvgPower kW. – Harati Sep 30 '22 at 13:30

0 Answers0