Consulting

Results 1 to 3 of 3

Thread: VBA Code Assistance

  1. #1
    VBAX Newbie
    Joined
    Mar 2017
    Posts
    2
    Location

    VBA Code Assistance

    I am trying to copy cells from an unopened file into a new file. I am able to do that - but when i run the program a second time the code overwrites the data imported the first time. Below is the code i am using for the first one. What do i need to change in the second file?

    'credit for this technique goes to John Walkenback

    Sub GetDataDemo()
    
          Dim FilePath$, Row&, Column&, Address$
          
          'change constants & FilePath below to suit
          '***************************************
          Const FileName$ = "OnlineCourses.xlsx"
          Const SheetName$ = "Sheet1"
          Const NumRows& = 14
          Const NumColumns& = 22
          FilePath = ActiveWorkbook.Path & "\"
          '***************************************
          
          DoEvents
          Application.ScreenUpdating = False
          If Dir(FilePath & FileName) = Empty Then
                MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist"
                Exit Sub
          End If
          For Row = 1 To NumRows
                For Column = 1 To NumColumns
                      Address = Cells(Row, Column).Address
                      Cells(Row, Column) = GetData(FilePath, FileName, SheetName, Address)
                      Columns.AutoFit
                Next Column
          Next Row
          ActiveWindow.DisplayZeros = False
    End Sub
    Private Function GetData(Path, File, Sheet, Address)
      Dim Data$
      Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
      Range(Address).Range("T1").Address(, , xlR1C1)
      GetData = ExecuteExcel4Macro(Data)
    End Function
    Last edited by SamT; 03-01-2017 at 03:51 PM.

  2. #2
    VBAX Newbie
    Joined
    Mar 2017
    Posts
    2
    Location
    I am wanting to run the program to gather information from 6 different spreadsheets and combining the data into one.

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Declare NextRow
     
    Dim NextRow as Long
    INsert this LastRow line where indicate
        DoEvents 
        Application.ScreenUpdating = False 
    
    NextRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
    
        If Dir(FilePath & FileName) = Empty Then
    Then Change the For Row = 1 To Line to
    For Row = NextRow to NumRows + NextRow
    Whoops! My bad. you said
    What do i need to change in the second file?
    In answer to that... Nothing. You need to change the Excel 4 Macro "Data."
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •