1

I made a program that should copy and paste data saved in a text file (which comes from a SAP system), in an Excel file except that when I start the program the table I made moves and the data sticks next to it.

I'm providing you with the piece of code that I think is problematic.

Sub OpenCSVFile()
     ' Load the CSV extract
    With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;" & fpath & "\" & ffilename, Destination:=Range("$A$1"))
    .Name = "text"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 850
    .TextFileStartRow = 4
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileOtherDelimiter = "|"
    .TextFileColumnDataTypes = Array(9, 9, 9, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
      1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
    End With
End Sub

I want to paste my data inside my table without moving my table and thus without sticking them next to it.

Before:

Before

After:

After

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
user11503878
  • 23
  • 4
  • 1
    Please read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Tim Stack Jun 06 '19 at 07:16
  • I'm sorry but there is nothing on your code moving your data @user11503878. This can't be the problem. Your problem may be inside your `OpenCSVFile` procedure. – Damian Jun 06 '19 at 07:20
  • Okay, I edited my post with the CVS file opening procedure. – user11503878 Jun 06 '19 at 07:23
  • @user11503878 can you paste images from the before and after? – Damian Jun 06 '19 at 07:24
  • @Damian Of course, it's okay, I put screenshots. – user11503878 Jun 06 '19 at 07:30
  • @Damian think the code does show part of the problem. When you add a data connection, other content it moved to the right or down! Essentially, he needs to bring in the data without adding a new connection each time. – rohrl77 Jun 06 '19 at 07:32
  • 1
    @rohrl77 yeah... his previous code wasn't this... Thought he deleted the previous that's why I asked him to show us this code. – Damian Jun 06 '19 at 07:34
  • @user11503878 like rohrl77 stated, your data is moving because of the connection. My advice? Create a temp sheet, paste your CSV and copy paste to your table. – Damian Jun 06 '19 at 07:36
  • The other thing is that my txt file doesn't look like the tut to what is copied so I don't understand. I will try to add a temp sheet. Thank you very much. – user11503878 Jun 06 '19 at 07:41

0 Answers0