none
How to get cell data from one excel file to another. RRS feed

  • Question

  • Can anyone help with this vba code please.

    I have 100 plus excel files stored locally C:\My Documents\Work\VBA\Core\E6130105     Note: E6130105 is one of the 100 plus docs.

    I have created a master core excel file and want to get some cell data from the E613 docs. individually.

    1) In the master file, I want to be asked which drawing number I want to open, e.g. E6130105 and it to be opened as read only.

    2) Secondly, I want to get cell data from the E613 docs. based on cell names, in the 100 plus docs I have cell names called  

    Core_Circle, Leg_Centres and HOW. The cell names are always across columns G,H,I but could be in any row.

    The cell data Core_Circle, Leg_Centres and HOW needs to be located A2,B2,C2 respectively in the master core excel file.

    I am unable to link excel files until my account is verified but can email docs if required.

    Thanks

    hmk999 


    Sunday, December 23, 2012 4:32 PM

Answers

  • See if the following macro works for you:

    Sub GetData()
        Dim wbkS As Workbook
        Dim wshS As Worksheet
        Dim wshT As Worksheet
        Dim strFile As String
        With Application.FileDialog(1) ' msoFileDialogOpen
            .Filters.Clear
            .Filters.Add "Excel workbooks", "*.xls;*.xlsx;*.xlsb;*.xlsm"
            .InitialFileName = "C:\My Documents\Work\VBA\Core\"
            If .Show Then
                strFile = .SelectedItems(1)
            Else
                MsgBox "No file selected", vbCritical
                Exit Sub
            End If
        End With
        Application.ScreenUpdating = False
        Set wshT = ActiveSheet
        Set wbkS = Workbooks.Open(Filename:=strFile, ReadOnly:=True)
        Set wshS = wbkS.Worksheets(1)
        wshT.Range("A2").Value = wshS.Range("Core_Circle").Value
        wshT.Range("B2").Value = wshS.Range("Leg_Centres").Value
        wshT.Range("C2").Value = wshS.Range("HOW").Value
        ' Optional: close source workbook
        wbkS.Close SaveChanges:=False
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar

    • Marked as answer by hmk999 Monday, December 24, 2012 3:33 PM
    Sunday, December 23, 2012 5:34 PM
  • Change the line

            wshT.Cells(TargetRow, TargetCol + i).Value = wshS.Cells(r, lngStartCol).Value

    to

            wshT.Cells(TargetRow, TargetCol + i).Value = Val(wshS.Cells(r, lngStartCol).Value)

    By the way, the code refers to the 1st sheet of the selected workbook, but in both your sample files, Drawings is the 2nd sheet, so you'd have to change

        Set wshS = wbkS.Worksheets(1)

    to

        Set wshS = wbkS.Worksheets(2)

    or

        Set wshS = wbkS.Worksheets("Drawing")


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by hmk999 Tuesday, December 10, 2013 11:39 PM
    Tuesday, December 10, 2013 10:27 PM
  • At the very beginning of the code, the constant TargetCol is defined:

        Const TargetCol = 8 ' Start pasting in column H

    The loop that pastes the data is

        For i = 1 To MaxSteps
            ...
            wshT.Cells(TargetRow, TargetCol + i).Value = ...
            ...
        Next i

    When the loop starts, i = 1, so TargetCol + i = 8 + 1 = 9. The 9th column is column I.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by hmk999 Tuesday, December 10, 2013 11:38 PM
    Tuesday, December 10, 2013 11:21 PM

All replies

  • Do the individual workbooks have one sheet each?

    Will the cells named Core_Circle, Leg_Centres and HOW always be in the same row in columns G, H and I, or could they be in different rows, e.g. Core_Circle in G30, Leg_Centres in H278 and HOW in I54?


    Regards, Hans Vogelaar

    Sunday, December 23, 2012 4:50 PM
  • Yes each E613 doc has just one sheet for each.

    The values of each named cell e.g. Core_Circle, Leg_centres and HOW will always be in merged cells G,H,I but could be in GHI 1 to GHI 65

    If I put my email address on here you could email me and I could send you my docs might be easier? thanks again for your help

    Sunday, December 23, 2012 5:04 PM
  • See if the following macro works for you:

    Sub GetData()
        Dim wbkS As Workbook
        Dim wshS As Worksheet
        Dim wshT As Worksheet
        Dim strFile As String
        With Application.FileDialog(1) ' msoFileDialogOpen
            .Filters.Clear
            .Filters.Add "Excel workbooks", "*.xls;*.xlsx;*.xlsb;*.xlsm"
            .InitialFileName = "C:\My Documents\Work\VBA\Core\"
            If .Show Then
                strFile = .SelectedItems(1)
            Else
                MsgBox "No file selected", vbCritical
                Exit Sub
            End If
        End With
        Application.ScreenUpdating = False
        Set wshT = ActiveSheet
        Set wbkS = Workbooks.Open(Filename:=strFile, ReadOnly:=True)
        Set wshS = wbkS.Worksheets(1)
        wshT.Range("A2").Value = wshS.Range("Core_Circle").Value
        wshT.Range("B2").Value = wshS.Range("Leg_Centres").Value
        wshT.Range("C2").Value = wshS.Range("HOW").Value
        ' Optional: close source workbook
        wbkS.Close SaveChanges:=False
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar

    • Marked as answer by hmk999 Monday, December 24, 2012 3:33 PM
    Sunday, December 23, 2012 5:34 PM
  • Thank you Hans I will give this a go and let you know.

    Best regards

    hmk999

    Sunday, December 23, 2012 5:45 PM
  • Hans, the macro is exactly what I wanted, I've tried it on 30 odd excel docs and get the information I need every time, what a genius you are.

    Thanks very much :)

    Sunday, December 23, 2012 8:45 PM
  • Hello Hans, I have tried the macro on every document and works everytime. I am just starting out learning VBA, would it be possible to give me some descriptions of the code? line by line, as to what is actually happening, i can read bits of it but would be a big help into any info you could give me.

    Thanks again

    hmk999

    Monday, December 24, 2012 3:31 PM
  • FileDialog is an 'object' that lets you display various dialogs, such as an 'Open' dialog and a 'Save As' dialog. Here, we tell Excel that we are going to use the 'Open' dialog:

        With Application.FileDialog(1) ' msoFileDialogOpen

    The 'With' means that we can refer to properties and methods of this object using . without anything before it, until End With.

    The next lines

            .Filters.Clear
           
    .Filters.Add "Excel workbooks", "*.xls;*.xlsx;*.xlsb;*.xlsm"

    clear any pre-existing file filters, and add a filter for Excel workbooks. This will be the only file type available in the file type dropdown.

    The line

           .InitialFileName = "C:\My Documents\Work\VBA\Core\"

    specifies the folder to be displayed.

    Next, we show the Open dialog. The Show method returns True if you click Open, and False if you cancel the dialog by clicking Cancel or the close button.

            If .Show Then
                strFile
    = .SelectedItems(1)
           
    Else
                MsgBox
    "No file selected", vbCritical
               
    Exit Sub
           
    End If

    If True, we assign the selected file path+name to the variable strPath; if False, we display a message box and exit the macro, since there is nothing to be done.

    The line End With indicates that we're done with the FileDialog.

    The rest of the macro will only be executed if you selected a file.

    We want to minimize the screen flicker that results from opening and closing a workbook in rapid succession; the line

        Application.ScreenUpdating = False

    takes care of this.

    We assign a few variables to keep track of what is what:

        Set wshT = ActiveSheet

    refers to the currently active sheet; I used the letter T to indicate 'Target'.


       
    Set wbkS = Workbooks.Open(Filename:=strFile, ReadOnly:=True)
       
    Set wshS = wbkS.Worksheets(1)

    opens the selected workbook as read-only and sets a variable to the first (and only) sheet in this workbook. The S in the variable names stand for 'Source'.

    The lines

        wshT.Range("A2").Value = wshS.Range("Core_Circle").Value
        wshT
    .Range("B2").Value = wshS.Range("Leg_Centres").Value
        wshT
    .Range("C2").Value = wshS.Range("HOW").Value

    transfer the values of the three named cells in the source sheet to A2:C2 in the target sheet.

    Finally, we close the workbook:

        wbkS.Close SaveChanges:=False

    and tell Excel to resume displaying changes:

        Application.ScreenUpdating = True


    Regards, Hans Vogelaar

    Monday, December 24, 2012 4:09 PM
  • Hans , Brilliant this is such a big help to me, thank you very much, merry christmas :)
    Monday, December 24, 2012 4:20 PM
  • Hans, would it be possible to add to the code and help me get some more data from my E613 Excel files into my master core document.

    In all the E613 docs, across rows K,L,M,N, there is always between 4 and 30 rows of data I want to get this data into my master core doc.

    Example 1 showing 4 rows of data

    Cell       K28      L28       M28      N28

    Value    400       400       25         25

    Cell       K29      L29       M29      N29

    Value    300       300       20         20

    Cell       K30      L30       M30      N30

    Value    200       200       15         15

    Cell       K31      L31       M31      N31

    Value    150       150       10         10

    Example 2 showing 30 rows of data (just typed the four out indicating the last cell being row 57).

    Cell       K28      L28       M28      N28

    Value    400       400       25         25

    Cell       K29      L29       M29      N29

    Value    300       300       20         20

    Cell       K30      L30       M30      N30

    Value    200       200       15         15

    Cell       K57      L57       M57      N57

    Value    150       150       10         10

    Notes:

    1. Not sure if they all start at K28 every time but I have named the cell Table_Start in all my E613 doc’s which will be position K28 in examples 1 & 2.
    2. In my master core excel file, I have left 30 spaces for column K, between I2 and AL2, for column L, between AM2 and BP2, for column M, between BQ2 and CT2, for column N, between CU2 and DX2.
    3. If there are less than 30 rows of data in any E613 doc, can the last value fill in the remaining cells so there is always a value? e.g. in example 1, the last K value is 150 at K31, so I want all the cells between L2 and AL2 in my master core doc to show 150 as the value. and have the same rule for columns L,M and N
    4. In my master core excel file I want to be told how many steps there are in cell D2, e.g. 1 step = 1 row, so in example 1 we have 4 steps, in example 2 we have 30 steps.

    Hope all this makes sense, let me know if you can help.

    hmk999

    Thursday, December 27, 2012 3:52 PM
  • Will the data columns K, L, M and N all end on the same row, or could they end on different rows, e.g. column K on K31, column L on L37, column M on L50 and column N on N43?

    Regards, Hans Vogelaar

    Thursday, December 27, 2012 4:16 PM
  • They will all start on the same row and all end on the same row everytime.

    Thanks

    hmk999

    Thursday, December 27, 2012 4:22 PM
  • Try this version. I have inserted some comments.

    Sub GetData()
        Dim wbkS As Workbook
        Dim wshS As Worksheet
        Dim wshT As Worksheet
        Dim strFile As String
        Dim lngStart As Long
        Dim lngEnd As Long
        Dim lngSteps As Long
        Dim i As Long
        Dim r As Long
        With Application.FileDialog(1) ' msoFileDialogOpen
            .Filters.Clear
            .Filters.Add "Excel workbooks", "*.xls;*.xlsx;*.xlsb;*.xlsm"
            .InitialFileName = "C:\My Documents\Work\VBA\Core\"
            If .Show Then
                strFile = .SelectedItems(1)
            Else
                MsgBox "No file selected", vbCritical
                Exit Sub
            End If
        End With
        Application.ScreenUpdating = False
        Set wshT = ActiveSheet
        Set wbkS = Workbooks.Open(Filename:=strFile, ReadOnly:=True)
        Set wshS = wbkS.Worksheets(1)
        wshT.Range("A2").Value = wshS.Range("Core_Circle").Value
        wshT.Range("B2").Value = wshS.Range("Leg_Centres").Value
        wshT.Range("C2").Value = wshS.Range("HOW").Value
        ' Get first data row
        lngStart = wshS.Range("Table_Start").Row
        ' Get last data row
        lngEnd = wshS.Range("K" & wshS.Rows.Count).End(xlUp).Row
        ' Calculate number of steps
        lngSteps = lngEnd - lngStart + 1
        ' Limit to 30 (shouldn't be used if all is well)
        If lngSteps > 30 Then
            MsgBox "More than 30 steps. Only copying the first 30.", vbInformation
            lngSteps = 30
            lngEnd = lngStart + 29
        End If
        ' Always fill 30 columns
        For i = 1 To 30
            ' Increase row number up to last data row
            If i <= lngSteps Then
                r = lngStart + i - 1
            End If
            ' Copy cell values
            wshT.Cells(2, 8 + i).Value = wshS.Cells(r, 11).Value
            wshT.Cells(2, 38 + i).Value = wshS.Cells(r, 12).Value
            wshT.Cells(2, 68 + i).Value = wshS.Cells(r, 13).Value
            wshT.Cells(2, 98 + i).Value = wshS.Cells(r, 14).Value
        Next i
        ' Enter number of steps in D2
        wshT.Range("D2").Value = lngSteps
        ' Optional: close source workbook
        wbkS.Close SaveChanges:=False
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar

    Thursday, December 27, 2012 4:43 PM
  • Thank you Hans I will try this and give you some feed back tomorrow

    Regards

    hmk999

    Thursday, December 27, 2012 8:18 PM
  • Hello Hans, very close to what I am looking for, just a couple of tweaks.

    1. I have tried the macro on 30 odd doc’s and every time the message “More than 30 steps, only copying the first 30” is shown, even with only four steps and every doc with under 30 steps? The message should never appear as there will never be over 30 steps. (I believe the maximum number of steps is actually 27 but I have created 30 in my master just in case).
    2. In this example I have four steps

         Cell       K28      L28       M28      N28

    Value    400       400       25         25

    Cell       K29      L29       M29      N29

    Value    300       300       20         20

    Cell       K30      L30       M30      N30

    Value    200       200       15         15

    Cell       K31      L31       M31      N31

    Value    150       150       10         10

    Can you fill in the remaining steps (blanks) with the last value so there’s a value to every cell? So this example would actually fill my master excel doc with the following data:

    Value          400     400     25        25

                        300     300     20        20

                        200     200     15        15       

                        150     150     10        10 - last step of four, copy these values into remaining cells everytime.

                        150     150     10        10

                        150     150     10        10

                        150     150     10        10

                        150     150     10        10

                        150     150     10        10

                        150     150     10        10

                        150     150     10        10

                        150     150     10        10

                        150     150     10        10

                        150     150     10        10

                        150     150     10        10

                        150     150     10        10

                        150     150     10        10

                        150     150     10        10

                        150     150     10        10

                        150     150     10        10

                        150     150     10        10

                        150     150     10        10

                        150     150     10        10

                        150     150     10        10

                        150     150     10        10

                        150     150     10        10

                        150     150     10        10

                        150     150     10        10

                        150     150     10        10

                        150     150     10        10 – last step (number 30)

    Thanks you

    Hmk999

    Friday, December 28, 2012 10:54 AM
  • I assumed that you wouldn't have data below the last "step". If there is information below it, the code will fail.

    Could you create a stripped-down copy of one of the "document" workbooks (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as Windows Live SkyDrive (https://skydrive.live.com), FileDropper (http://filedropper.com) or DropBox (http://www.dropbox.com). Then post a link to the uploaded and shared file here.

    Or register at www.eileenslounge.com (it's free) and post a message in the Excel forum. You can attach files up to 250 KB to a post there (zipped if necessary).


    Regards, Hans Vogelaar

    Friday, December 28, 2012 11:37 AM
  • https://skydrive.live.com/redir?resid=22F7BBABD7DEE83B!123&authkey=!AJcyUrNoOrToVHM

    Sorry to mess you about Hans, I've had alook and the maximum number of steps is 26, so can you change the code so In my master core excel file, I have left 26 spaces for column K, between I2 and AH2, for column L, between AI2 and BH2, for column M, between BI2 and CH2, for column N, between CI2 and DH2.

    The only other thing to point out is the data doesnt necessary start at row 28 everytime.

    Regards

    hmk999

    Friday, December 28, 2012 12:49 PM
  • There are indeed data beneath the data table. And I don't know how the Drawing sheet is populated, but the cells immediately below the last row of the data table look blank, but they aren't, so Excel thinks the table continues further down than you intend. I had to rewrite the code to take this into account.

    I have placed a few constants at the beginning of the code; you can easily modify these if the layout changes.

    Sub GetData()
        Const TargetRow = 2 ' Paste into row 2
        Const TargetCol = 8 ' Start pasting in column H
        Const MaxSteps = 26
        Dim wbkS As Workbook
        Dim wshS As Worksheet
        Dim wshT As Worksheet
        Dim strFile As String
        Dim lngStart As Long
        Dim lngEnd As Long
        Dim lngSteps As Long
        Dim lngStartCol As Long
        Dim i As Long
        Dim r As Long
        With Application.FileDialog(1) ' msoFileDialogOpen
            .Filters.Clear
            .Filters.Add "Excel workbooks", "*.xls;*.xlsx;*.xlsb;*.xlsm"
            '.InitialFileName = "C:\My Documents\Work\VBA\Core\"
            If .Show Then
                strFile = .SelectedItems(1)
            Else
                MsgBox "No file selected", vbCritical
                Exit Sub
            End If
        End With
        Application.ScreenUpdating = False
        Set wshT = ActiveSheet
        Set wbkS = Workbooks.Open(Filename:=strFile, ReadOnly:=True)
        Set wshS = wbkS.Worksheets(1)
        wshT.Cells(TargetRow, 1).Value = wshS.Range("Core_Circle").Value
        wshT.Cells(TargetRow, 2).Value = wshS.Range("Leg_Ctrs").Value
        wshT.Cells(TargetRow, 3).Value = wshS.Range("HOW").Value
        ' Get first data row
        lngStart = wshS.Range("Table_Start").Row
        lngStartCol = wshS.Range("Table_Start").Column
        ' Get last data row
        For i = 1 To MaxSteps - 1
            If wshS.Range("Table_Start").Offset(RowOffset:=i).Value = "" Then
                Exit For
            End If
        Next i
        lngEnd = lngStart + i - 1
        ' Calculate number of steps
        lngSteps = i
        ' Always fill MaxSteps columns
        For i = 1 To MaxSteps
            ' Increase row number up to last data row
            If i <= lngSteps Then
                r = lngStart + i - 1
            End If
            ' Copy cell values
            wshT.Cells(TargetRow, TargetCol + i).Value = wshS.Cells(r, lngStartCol).Value
            wshT.Cells(TargetRow, TargetCol + MaxSteps + i).Value = wshS.Cells(r, lngStartCol + 1).Value
            wshT.Cells(TargetRow, TargetCol + 2 * MaxSteps + i).Value = wshS.Cells(r, lngStartCol + 2).Value
            wshT.Cells(TargetRow, TargetCol + 3 * MaxSteps + i).Value = wshS.Cells(r, lngStartCol + 3).Value
        Next i
        ' Enter number of steps
        wshT.Cells(TargetRow, 4).Value = lngSteps
        ' Optional: close source workbook
        wbkS.Close SaveChanges:=False
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar

    Friday, December 28, 2012 1:28 PM
  • Hans, I have tried this out on 30 plus doc's and it works every time, THANK YOU, you make writing the code look easy.

    Over the next couple of days i will keep going over the code so i fully understand it, if i have any questions i will come back to you,

    Thanks again you've been a big help :)

    Best regards

    hmk999

    Friday, December 28, 2012 2:46 PM
  • Hans, I know you previously gave me a break down of the code, would it be possible to give me some descriptions on what is happening in the code, particular with the Table_start data, just trying to understand a little better. When i start to read the code, straight away where you show const TargetCol = 8 ' start pasting in Column H, but it actually starts pasting = 9 in Column I, is this to do with the ofset you have put in the code?

    Regards

    hmk999

    Friday, December 28, 2012 4:17 PM
  • The loop that copies the values begins with

    For i = 1 To MaxSteps

    and it then has

    wshT.Cells(TargetRow, TargetCol + i).Value = wshS.Cells(r, lngStartCol).Value

    The first time round, i = 1, so the value of the cell in TargetCol + 1 is set. So yes, we actually start copying in column 9 = column I, not in column 8 = column H.


    Regards, Hans Vogelaar

    Friday, December 28, 2012 4:48 PM
  • Thank you Hans I understand what you are saying.

    hmk999

    Saturday, December 29, 2012 10:45 AM
  • You were very kind and patient to explain this code for the inquirer.  I hope you know how much this is helping VBA beginners like me also.  Thank you so much for your responses.
    Saturday, December 29, 2012 2:39 PM
  • Thanks for your kind words, much appreciated!

    Regards, Hans Vogelaar

    Saturday, December 29, 2012 2:47 PM
  • Thanks very much for all your help Hans, you have been a big help to me as a VBA beginner, very much appreciated.

    Best regards

    hmk999

    Sunday, December 30, 2012 1:42 PM
  • https://skydrive.live.com/redir?resid=22F7BBABD7DEE83B!125&authkey=!ADkL4l_YmhF7PK8

    https://skydrive.live.com/redir?resid=22F7BBABD7DEE83B!126&authkey=!APMDK-MgW0T2C1w

    Hans, I have added a couple more things to the code and all working fine. If you look at the links for the two examples you will see that one doc has a value in cell names Hole_position_A and Hole_position_B, but the other doc only has a value in Hole_position_A.

    From my entire E613 doc’s half have values in both cells and the remaining doc’s in Hole_position_A only.

    Can you adjust and highlight the code so that there will always be a value in my master excel doc?  If there is no value in Hole_position_B, (TargetRow, 8) in the E613 doc’s, it will use the same value from Hole_position_A.

    I’ve had a play around with it but I’m doing something wrong.

    Best regards

    Hmk999

    Sub GetData()
        Const TargetRow = 2 ' Paste into row 2
        Const TargetCol = 8 ' Start pasting in column H
        Const MaxSteps = 26
        Dim wbkS As Workbook
        Dim wshS As Worksheet
        Dim wshT As Worksheet
        Dim strFile As String
        Dim lngStart As Long
        Dim lngEnd As Long
        Dim lngSteps As Long
        Dim lngStartCol As Long
        Dim i As Long
        Dim r As Long
        With Application.FileDialog(1) ' msoFileDialogOpen
            .Filters.Clear
            .Filters.Add "Excel workbooks", "*.xls;*.xlsx;*.xlsb;*.xlsm"
            .InitialFileName = "S:\Design\LIVE Contract Designs\Core Schedules\613xxxx"
            If .Show Then
                strFile = .SelectedItems(1)
            Else
                MsgBox "No file selected", vbCritical
                Exit Sub
            End If
        End With
        Application.ScreenUpdating = False
        Set wshT = ActiveSheet
        Set wbkS = Workbooks.Open(Filename:=strFile, ReadOnly:=True)
        Set wshS = wbkS.Worksheets(1)
        wshT.Cells(TargetRow, 1).Value = wshS.Range("Core_Circle").Value
        wshT.Cells(TargetRow, 2).Value = wshS.Range("Leg_Ctrs").Value
        wshT.Cells(TargetRow, 3).Value = wshS.Range("HOW").Value
        wshT.Cells(TargetRow, 5).Value = wshS.Range("No_of_Holes").Value
        wshT.Cells(TargetRow, 6).Value = wshS.Range("Hole_Diameter").Value
        wshT.Cells(TargetRow, 7).Value = wshS.Range("Hole_Position_A").Value
        wshT.Cells(TargetRow, 8).Value = wshS.Range("Hole_Position_B").Value
        ' Get first data row
        lngStart = wshS.Range("Table_Start").Row
        lngStartCol = wshS.Range("Table_Start").Column
        ' Get last data row
        For i = 1 To MaxSteps - 1
            If wshS.Range("Table_Start").Offset(RowOffset:=i).Value = "" Then
                Exit For
            End If
        Next i
        lngEnd = lngStart + i - 1
        ' Calculate number of steps
        lngSteps = i
        ' Always fill MaxSteps columns
        For i = 1 To MaxSteps
            ' Increase row number up to last data row
            If i <= lngSteps Then
                r = lngStart + i - 1
            End If
            ' Copy cell values
            wshT.Cells(TargetRow, TargetCol + i).Value = wshS.Cells(r, lngStartCol).Value
            wshT.Cells(TargetRow, TargetCol + MaxSteps + i).Value = wshS.Cells(r, lngStartCol + 1).Value
            wshT.Cells(TargetRow, TargetCol + 2 * MaxSteps + i).Value = wshS.Cells(r, lngStartCol + 2).Value
            wshT.Cells(TargetRow, TargetCol + 3 * MaxSteps + i).Value = wshS.Cells(r, lngStartCol + 3).Value
        Next i
        ' Enter number of steps
        wshT.Cells(TargetRow, 4).Value = lngSteps
        ' Optional: close source workbook
        wbkS.Close SaveChanges:=False
        Application.ScreenUpdating = True
    End Sub

    Sunday, January 6, 2013 8:58 AM
  • Change the line

        wshT.Cells(TargetRow, 8).Value = wshS.Range("Hole_Position_B").Value

    to

        If wshS.Range("Hole_Position_B").Value = "" Then
            wshT.Cells(TargetRow, 8).Value = wshS.Range("Hole_Position_A").Value
        Else
            wshT.Cells(TargetRow, 8).Value = wshS.Range("Hole_Position_B").Value
        End If


    Regards, Hans Vogelaar

    Sunday, January 6, 2013 11:44 AM
  • Brilliant Hans, Thanks for your help once again it is much appreciated.

    Best regards

    hmk999

    Sunday, January 6, 2013 8:55 PM
  • https://skydrive.live.com/redir?resid=22F7BBABD7DEE83B!129&authkey=!ANpo0krDCofjE_w

    https://skydrive.live.com/redir?resid=22F7BBABD7DEE83B!131&authkey=!AM6s2yxzzYBU0_8

    Hi Hans, Can you please help to modify my code again. (Latest code below)

    If you look at the attachments Test 1 & Test 2, you will notice I have added two more named cells, Progressive_HV and Progressive_LV.

    In my master excel document, I want to replace the HV step size data with the HV progressive data and replace the LV step size data with the LV progressive data. Using the same technique so if there are 4 steps or 26 steps in any E613 docs, the last value will always fill in the remaining cells.

    In my master excel doc, the Progressive_HV data should start at cell BI and finish at CH (26 steps) and Progressive_LV data should start at cell CI and finish at DH (26 steps) (all on row 2)

    I no longer require the step size HV and LV data.

    Best regards

    hmk

    Sub GetData()
        Const TargetRow = 2 ' Paste into row 2
        Const TargetCol = 8 ' Start pasting in column H
        Const MaxSteps = 26
        Dim wbkS As Workbook
        Dim wshS As Worksheet
        Dim wshT As Worksheet
        Dim strFile As String
        Dim lngStart As Long
        Dim lngEnd As Long
        Dim lngSteps As Long
        Dim lngStartCol As Long
        Dim i As Long
        Dim r As Long
        With Application.FileDialog(1) ' msoFileDialogOpen
            .Filters.Clear
            .Filters.Add "Excel workbooks", "*.xls;*.xlsx;*.xlsb;*.xlsm"
            .InitialFileName = "S:\Design\LIVE Contract Designs\Core Schedules\613xxxx"
            If .Show Then
                strFile = .SelectedItems(1)
            Else
                MsgBox "No file selected", vbCritical
                Exit Sub
            End If
        End With
        Application.ScreenUpdating = False
        Set wshT = ActiveSheet
        Set wbkS = Workbooks.Open(Filename:=strFile, ReadOnly:=True)
        Set wshS = wbkS.Worksheets(1)
        wshT.Cells(TargetRow, 1).Value = wshS.Range("Core_Circle").Value
        wshT.Cells(TargetRow, 2).Value = wshS.Range("Leg_Ctrs").Value
        wshT.Cells(TargetRow, 3).Value = wshS.Range("HOW").Value
        wshT.Cells(TargetRow, 5).Value = wshS.Range("No_of_Holes").Value
        wshT.Cells(TargetRow, 6).Value = wshS.Range("Hole_Diameter").Value
        wshT.Cells(TargetRow, 7).Value = wshS.Range("Hole_Position_A").Value
       
        If wshS.Range("Hole_Position_B").Value = "" Then
            wshT.Cells(TargetRow, 8).Value = wshS.Range("Hole_Position_A").Value
        Else
            wshT.Cells(TargetRow, 8).Value = wshS.Range("Hole_Position_B").Value
        End If

        ' Get first data row
        lngStart = wshS.Range("Table_Start").Row
        lngStartCol = wshS.Range("Table_Start").Column
        ' Get last data row
        For i = 1 To MaxSteps - 1
            If wshS.Range("Table_Start").Offset(RowOffset:=i).Value = "" Then
                Exit For
            End If
        Next i
        lngEnd = lngStart + i - 1
        ' Calculate number of steps
        lngSteps = i
        ' Always fill MaxSteps columns
        For i = 1 To MaxSteps
            ' Increase row number up to last data row
            If i <= lngSteps Then
                r = lngStart + i - 1
            End If
            ' Copy cell values
            wshT.Cells(TargetRow, TargetCol + i).Value = wshS.Cells(r, lngStartCol).Value
            wshT.Cells(TargetRow, TargetCol + MaxSteps + i).Value = wshS.Cells(r, lngStartCol + 1).Value
            wshT.Cells(TargetRow, TargetCol + 2 * MaxSteps + i).Value = wshS.Cells(r, lngStartCol + 2).Value
            wshT.Cells(TargetRow, TargetCol + 3 * MaxSteps + i).Value = wshS.Cells(r, lngStartCol + 3).Value
        Next i
        ' Enter number of steps
        wshT.Cells(TargetRow, 4).Value = lngSteps
        ' Optional: close source workbook
        wbkS.Close SaveChanges:=False
        Application.ScreenUpdating = True
    End Sub

    Tuesday, January 15, 2013 11:57 AM
  • Does this do what you want?

    Sub GetData()
        Const TargetRow = 2 ' Paste into row 2
        Const TargetCol = 8 ' Start pasting in column H
        Const MaxSteps = 26
        Dim wbkS As Workbook
        Dim wshS As Worksheet
        Dim wshT As Worksheet
        Dim strFile As String
        Dim lngStart As Long
        Dim lngEnd As Long
        Dim lngSteps As Long
        Dim lngStartCol As Long
        Dim lngHVCol As Long
        Dim lngLVCol As Long
        Dim i As Long
        Dim r As Long
        With Application.FileDialog(1) ' msoFileDialogOpen
            .Filters.Clear
            .Filters.Add "Excel workbooks", "*.xls;*.xlsx;*.xlsb;*.xlsm"
            .InitialFileName = "S:\Design\LIVE Contract Designs\Core Schedules\613xxxx"
            If .Show Then
                strFile = .SelectedItems(1)
            Else
                MsgBox "No file selected", vbCritical
                Exit Sub
            End If
        End With
        Application.ScreenUpdating = False
        Set wshT = ActiveSheet
        Set wbkS = Workbooks.Open(Filename:=strFile, ReadOnly:=True)
        Set wshS = wbkS.Worksheets(1)
        wshT.Cells(TargetRow, 1).Value = wshS.Range("Core_Circle").Value
        wshT.Cells(TargetRow, 2).Value = wshS.Range("Leg_Ctrs").Value
        wshT.Cells(TargetRow, 3).Value = wshS.Range("HOW").Value
        wshT.Cells(TargetRow, 5).Value = wshS.Range("No_of_Holes").Value
        wshT.Cells(TargetRow, 6).Value = wshS.Range("Hole_Diameter").Value
        wshT.Cells(TargetRow, 7).Value = wshS.Range("Hole_Position_A").Value
        
       If wshS.Range("Hole_Position_B").Value = "" Then
            wshT.Cells(TargetRow, 8).Value = wshS.Range("Hole_Position_A").Value
        Else
            wshT.Cells(TargetRow, 8).Value = wshS.Range("Hole_Position_B").Value
        End If
    
       ' Get first data row
        lngStart = wshS.Range("Table_Start").Row
        lngStartCol = wshS.Range("Table_Start").Column
        lngHVCol = wshS.Range("Progressive_HV").Column
        lngLVCol = wshS.Range("Progressive_LV").Column
        ' Get last data row
        For i = 1 To MaxSteps - 1
            If wshS.Range("Table_Start").Offset(RowOffset:=i).Value = "" Then
                Exit For
            End If
        Next i
        lngEnd = lngStart + i - 1
        ' Calculate number of steps
        lngSteps = i
        ' Always fill MaxSteps columns
        For i = 1 To MaxSteps
            ' Increase row number up to last data row
            If i <= lngSteps Then
                r = lngStart + i - 1
            End If
            ' Copy cell values
            wshT.Cells(TargetRow, TargetCol + i).Value = wshS.Cells(r, lngStartCol).Value
            wshT.Cells(TargetRow, TargetCol + MaxSteps + i).Value = wshS.Cells(r, lngStartCol + 1).Value
            wshT.Cells(TargetRow, TargetCol + 2 * MaxSteps + i).Value = wshS.Cells(r, lngHVCol).Value
            wshT.Cells(TargetRow, TargetCol + 3 * MaxSteps + i).Value = wshS.Cells(r, lngLVCol).Value
        Next i
        ' Enter number of steps
        wshT.Cells(TargetRow, 4).Value = lngSteps
        ' Optional: close source workbook
        wbkS.Close SaveChanges:=False
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar

    Tuesday, January 15, 2013 5:54 PM
  • Hans, tried this on 30 odd documents and this is exactly what I want, brilliant, thanks again for your help much appreciated.

    Hmk999

    Wednesday, January 16, 2013 8:58 AM
  • Hi,

    I tried to adapt this to my issue but I can't seem to figure it out. No experience with VBA or macros. I am coping and pasting the contents of a single cell at the very end of two columns E and P, into a master excel file that contains the cell contents of these different excel workbooks. I have to match the contents of these cells to the file name. 

    So my master workbook is set up like this

    Project Remaining Total

    d44444 40,000 50,000

    d343434 44334 99999

    so the new file has a new row for each file I go through. 

    If it is possible, If i have the file name in the excel file is possible for it to cycle thought and find the file in the folder and keep cycling till the list is complete. 

    Please help, thank you,

    YOGI

    Monday, May 27, 2013 6:25 PM
  • Hello Yogi4,

    Does the master sheet already contain the file names?


    Regards, Hans Vogelaar

    Monday, May 27, 2013 8:59 PM
  • yes but without the file extensions. Thank you for getting back to me!

    Tuesday, May 28, 2013 2:15 PM
  • See if this macro does what you want:

    Sub GetData()
        ' Modify as needed; keep trailing backslash
        Const strPath = "C:\MyFiles\"
        Const lngFirstRow = 2
        Dim strFile As String
        Dim wshTrg As Worksheet
        Dim wbkSrc As Workbook
        Dim wshSrc As Worksheet
        Dim lngCurRow As Long
        Dim lngLastRow As Long
        ' Target sheet
        Set wshTrg = ActiveSheet
        ' Get last row
        lngLastRow = wshTrg.Range("A" & wshTrg.Rows.Count) _
            .End(xlUp).Row
        ' Loop through rows
        For lngCurRow = lngFirstRow To lngLastRow
            ' Look for file
            strFile = Dir(strPath & wshSrc.Range("A" & lngCurRow) _
                .Value & "*.xls*")
            ' Did we find it?
            If strFile <> "" Then
                ' Open the workbook
                Set wbkSrc = Workbooks.Open _
                    (Filename:=strPath & strFile)
                ' Refer to its first sheet
                Set wshSrc = wbkSrc.Worksheets(1)
                ' Copy last cell in column E to column B
                wshSrc.Range("E" & wshSrc.Rows.Count) _
                    .End(xlUp).Copy _
                    Destination:=wshTrg.Range("B" & lngCurRow)
                ' Copy last cell in column P to column C
                wshSrc.Range("P" & wshSrc.Rows.Count) _
                    .End(xlUp).Copy _
                    Destination:=wshTrg.Range("C" & lngCurRow)
                ' Close workbook
                wbkSrc.Close SaveChanges:=False
            End If
        Next lngCurRow
    End Sub


    Regards, Hans Vogelaar

    Tuesday, May 28, 2013 4:11 PM
  • Thank you so much but I feel like I might not be doing something correctly to make it work.

    there is an error that comes up.

    strFile = Dir(strPath & wshSrc.Range("A" & lngCurRow) _
                .Value & "*.xls*")

    This section is highlighted and says Runtime error 91 "Object Variable or with block variable not set"

    I really appreciate your help with this. Thanks!

    Yogi

    Wednesday, May 29, 2013 1:48 PM
  • Sorry, my bad! I made a mistake in that line, it should be

            strFile = Dir(strPath & wshTrg.Range("A" & lngCurRow) _
                .Value & "*.xls*")

    (i.e. wshTrg instead of wshSrc)


    Regards, Hans Vogelaar

    Wednesday, May 29, 2013 2:53 PM
  • I think it should work because I get no error messages when I press run but it doesn't seem to do anything. I opened my file than opened the macros menu and i pressed run, it gives me a working cursor but nothing shows up in my excel file.

    I tried googling to find how to make it function but they talk about recording and things. Sorry about this. thank you so much

    Yogi

    Wednesday, May 29, 2013 3:45 PM
  • 1) Did you change the value of the constant strPath in the line

        Const strPath = "C:\MyFiles\"

    You should replace C:\MyFiles with the path of the folder containing the workbooks.

    2) I assumed that the file names are in column A, and that you want to copy values from the other workbooks into columns B and C.

    If the file names are in another column, you'll have to change the code accordingly.


    Regards, Hans Vogelaar

    Wednesday, May 29, 2013 4:10 PM
  • yes I made all the necessary changes. Am i supposed to highlight the cells the Macro effects, I tried that too.
    Wednesday, May 29, 2013 4:15 PM
  • No, there should be no need to highlight cells.

    Try the following:

    Click anywhere in the macro (in the Visual Basic Editor).

    Each time you press the function key F8, one step of the macro will be executed, then it will pause. The line to be executed next will be highlighted.

    While the code is paused, you can hover the mouse pointer over variables in the code to see their value in a tooltip.

    This allows you to follow the execution of the code in detail. Hopefully, this will provide a clue.


    Regards, Hans Vogelaar

    Wednesday, May 29, 2013 4:36 PM
  • I am not too sure what to look for. But I noticed that it skips from

    ' Did we find it?
            If strFile <> "" Then

    TO

     End If

    But I know the file is there in the directory.

    It does look throught the list and moves to the next file number in the list but doesn't excute any of the inbetween functions.

    Thanks

    Wednesday, May 29, 2013 5:09 PM
  • As long as i put in the path to the work book it shouldn't matter where it is even if its on a server?
    Wednesday, May 29, 2013 5:11 PM
  • Do you mean that the cells in column A contain the path AND filename?


    Regards, Hans Vogelaar

    Wednesday, May 29, 2013 7:50 PM
  • No, its just the file name.

    Thursday, May 30, 2013 8:00 PM
  • There must be a spelling difference or error somewhere - apparently the file path + file name is not recognized.

    Regards, Hans Vogelaar

    Thursday, May 30, 2013 8:50 PM
  • Thanks Hans. It interesting to just see the code.
    Monday, June 3, 2013 3:38 PM
  • Does this do what you want?

    Sub GetData()
        Const TargetRow = 2 ' Paste into row 2
        Const TargetCol = 8 ' Start pasting in column H
        Const MaxSteps = 26
        Dim wbkS As Workbook
        Dim wshS As Worksheet
        Dim wshT As Worksheet
        Dim strFile As String
        Dim lngStart As Long
        Dim lngEnd As Long
        Dim lngSteps As Long
        Dim lngStartCol As Long
        Dim lngHVCol As Long
        Dim lngLVCol As Long
        Dim i As Long
        Dim r As Long
        With Application.FileDialog(1) ' msoFileDialogOpen
            .Filters.Clear
            .Filters.Add "Excel workbooks", "*.xls;*.xlsx;*.xlsb;*.xlsm"
            .InitialFileName = "S:\Design\LIVE Contract Designs\Core Schedules\613xxxx"
            If .Show Then
                strFile = .SelectedItems(1)
            Else
                MsgBox "No file selected", vbCritical
                Exit Sub
            End If
        End With
        Application.ScreenUpdating = False
        Set wshT = ActiveSheet
        Set wbkS = Workbooks.Open(Filename:=strFile, ReadOnly:=True)
        Set wshS = wbkS.Worksheets(1)
        wshT.Cells(TargetRow, 1).Value = wshS.Range("Core_Circle").Value
        wshT.Cells(TargetRow, 2).Value = wshS.Range("Leg_Ctrs").Value
        wshT.Cells(TargetRow, 3).Value = wshS.Range("HOW").Value
        wshT.Cells(TargetRow, 5).Value = wshS.Range("No_of_Holes").Value
        wshT.Cells(TargetRow, 6).Value = wshS.Range("Hole_Diameter").Value
        wshT.Cells(TargetRow, 7).Value = wshS.Range("Hole_Position_A").Value
        
       If wshS.Range("Hole_Position_B").Value = "" Then
            wshT.Cells(TargetRow, 8).Value = wshS.Range("Hole_Position_A").Value
        Else
            wshT.Cells(TargetRow, 8).Value = wshS.Range("Hole_Position_B").Value
        End If
    
       ' Get first data row
        lngStart = wshS.Range("Table_Start").Row
        lngStartCol = wshS.Range("Table_Start").Column
        lngHVCol = wshS.Range("Progressive_HV").Column
        lngLVCol = wshS.Range("Progressive_LV").Column
        ' Get last data row
        For i = 1 To MaxSteps - 1
            If wshS.Range("Table_Start").Offset(RowOffset:=i).Value = "" Then
                Exit For
            End If
        Next i
        lngEnd = lngStart + i - 1
        ' Calculate number of steps
        lngSteps = i
        ' Always fill MaxSteps columns
        For i = 1 To MaxSteps
            ' Increase row number up to last data row
            If i <= lngSteps Then
                r = lngStart + i - 1
            End If
            ' Copy cell values
            wshT.Cells(TargetRow, TargetCol + i).Value = wshS.Cells(r, lngStartCol).Value
            wshT.Cells(TargetRow, TargetCol + MaxSteps + i).Value = wshS.Cells(r, lngStartCol + 1).Value
            wshT.Cells(TargetRow, TargetCol + 2 * MaxSteps + i).Value = wshS.Cells(r, lngHVCol).Value
            wshT.Cells(TargetRow, TargetCol + 3 * MaxSteps + i).Value = wshS.Cells(r, lngLVCol).Value
        Next i
        ' Enter number of steps
        wshT.Cells(TargetRow, 4).Value = lngSteps
        ' Optional: close source workbook
        wbkS.Close SaveChanges:=False
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar


    Hi Hans,
    Can you help me to modify my code again (latest code above)

    If you look at the two attachments, 4 steps and 17 steps, you will notice that there are
    two Asterisk at the location of the last two steps, down the Leg column.

    For Info, I have over 100 E613 docs I use this code for, all have different values between
    4 and 26 steps. Only a few of the docs have the Asterisk as discussed.

    The problem I have, when I run the macro to collect the data to my master excel doc, it
    fetchers the number and the Asterisk e.g. 70 * or 60 * or 190 * or 170 * (as per attachments)

    Can you modify my code so it just collects the number and not the Asterisk?

    More info, the Asterisk will only ever appear on the last two steps of the leg column but
    there can be between 4 and 26 steps. It can be a two or three digit number e.g.
    70 * or 190 *. Also there will always be a space between the last number and the Asterisk.

    Hope this makes sense, let me know otherwise.

    Best
    Regards

    hmk999




    • Edited by hmk999 Tuesday, December 10, 2013 10:49 PM Remove links
    Tuesday, December 10, 2013 7:16 PM
  • Change the line

            wshT.Cells(TargetRow, TargetCol + i).Value = wshS.Cells(r, lngStartCol).Value

    to

            wshT.Cells(TargetRow, TargetCol + i).Value = Val(wshS.Cells(r, lngStartCol).Value)

    By the way, the code refers to the 1st sheet of the selected workbook, but in both your sample files, Drawings is the 2nd sheet, so you'd have to change

        Set wshS = wbkS.Worksheets(1)

    to

        Set wshS = wbkS.Worksheets(2)

    or

        Set wshS = wbkS.Worksheets("Drawing")


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by hmk999 Tuesday, December 10, 2013 11:39 PM
    Tuesday, December 10, 2013 10:27 PM
  • Thanks Hans,

    When I run the macro and all the data is collected, the "START TABLE" starts at location cell I in my master doc.

    Can you point out to me, where in the code tells the "START TABLE to start at location I, I can read everything else but was unsure about this.

    I have registered at eileenslounge.com tonight, forum looks really good, will post future problems I have there.

    Regards

    hmk999 

    Tuesday, December 10, 2013 11:08 PM
  • At the very beginning of the code, the constant TargetCol is defined:

        Const TargetCol = 8 ' Start pasting in column H

    The loop that pastes the data is

        For i = 1 To MaxSteps
            ...
            wshT.Cells(TargetRow, TargetCol + i).Value = ...
            ...
        Next i

    When the loop starts, i = 1, so TargetCol + i = 8 + 1 = 9. The 9th column is column I.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by hmk999 Tuesday, December 10, 2013 11:38 PM
    Tuesday, December 10, 2013 11:21 PM
  • Thanks Hans, code works fine every time.

    I have studied the code and the bit I'm still unsure about is the loop, I understand the rest.

    Say if I wanted to change the code for the "TABLE START" to start putting the data in column M of my master doc.

    Could you show me which parts of the code to change to do this? I tried a couple of things but didn't work.

    I don't need to change the code I'm just trying to understand this area a little more.

    Thanks again

    hmk999

    Wednesday, December 11, 2013 8:55 PM
  • The first few columns are hard-coded: the value of Core_Circle is pasted into column 1 (i.e. A), that of Leg_Ctrs into column 2 (i.e. B):

        wshT.Cells(TargetRow, 1).Value = wshS.Range("Core_Circle").Value
        wshT
    .Cells(TargetRow, 2).Value = wshS.Range("Leg_Ctrs").Value

    etc.

    The constant TargetCol has value 8; this corresponds to column H. The loop actually pastes the variable values to the right of column 8: it starts at TargetCol+1 =9, i.e. column I.

    If you want to start pasting these values at column M, i.e. column 13, you should set TargetCol to 1 less, i.e. to 12:

        Const TargetCol = 12

    and if you want to start at column AF, i.e. column 32, set TargetCol to 31:

        Const TargetCol = 31


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, December 11, 2013 9:18 PM
  • Thank you Hans, your help is much appreciated.

    Best Regards

    hmk999

    Friday, December 13, 2013 10:08 PM
  • Sub GetData()
        Const TargetRow = 2 ' Paste into row 2
        Const TargetCol = 8 ' Start pasting in column H
        Const MaxSteps = 26
        Dim wbkS As Workbook
        Dim wshS As Worksheet
        Dim wshT As Worksheet
        Dim strFile As String
        Dim lngStart As Long
        Dim lngEnd As Long
        Dim lngSteps As Long
        Dim lngStartCol As Long
        Dim lngHVCol As Long
        Dim lngLVCol As Long
        Dim i As Long
        Dim r As Long
        With Application.FileDialog(1) ' msoFileDialogOpen
            .Filters.Clear
            .Filters.Add "Excel workbooks", "*.xls;*.xlsx;*.xlsb;*.xlsm"
            .InitialFileName = "W:\Eng\Transformers\7_Engineering_Library\1_Electrical\2_Electrical_Core_Schedules\613xxxx\Development_hmk"
            If .Show Then
                strFile = .SelectedItems(1)
            Else
                MsgBox "No file selected", vbCritical
                Exit Sub
            End If
        End With
        Application.ScreenUpdating = False
        Set wshT = ActiveSheet
        Set wbkS = Workbooks.Open(Filename:=strFile, ReadOnly:=True)
        Set wshS = wbkS.Worksheets(1)
        wshT.Cells(TargetRow, 1).Value = wshS.Range("Core_Circle").Value
        wshT.Cells(TargetRow, 2).Value = wshS.Range("Leg_Ctrs").Value
        wshT.Cells(TargetRow, 3).Value = wshS.Range("HOW").Value
        wshT.Cells(TargetRow, 5).Value = wshS.Range("No_of_Holes").Value
        wshT.Cells(TargetRow, 6).Value = wshS.Range("Hole_Diameter").Value
        wshT.Cells(TargetRow, 7).Value = wshS.Range("Hole_Position_A").Value
       
       If wshS.Range("Hole_Position_B").Value = "" Then
            wshT.Cells(TargetRow, 8).Value = wshS.Range("Hole_Position_A").Value
        Else
            wshT.Cells(TargetRow, 8).Value = wshS.Range("Hole_Position_B").Value
        End If

       ' Get first data row
        lngStart = wshS.Range("Table_Start").Row
        lngStartCol = wshS.Range("Table_Start").Column
        lngHVCol = wshS.Range("Progressive_HV").Column
        lngLVCol = wshS.Range("Progressive_LV").Column
        ' Get last data row
        For i = 1 To MaxSteps - 1
            If wshS.Range("Table_Start").Offset(RowOffset:=i).Value = "" Then
                Exit For
            End If
        Next i
        lngEnd = lngStart + i - 1
        ' Calculate number of steps
        lngSteps = i
        ' Always fill MaxSteps columns
        For i = 1 To MaxSteps
            ' Increase row number up to last data row
            If i <= lngSteps Then
                r = lngStart + i - 1
            End If
            ' Copy cell values
            wshT.Cells(TargetRow, TargetCol + i).Value = Val(wshS.Cells(r, lngStartCol).Value)
            wshT.Cells(TargetRow, TargetCol + MaxSteps + i).Value = wshS.Cells(r, lngStartCol + 1).Value
            wshT.Cells(TargetRow, TargetCol + 2 * MaxSteps + i).Value = wshS.Cells(r, lngHVCol).Value
            wshT.Cells(TargetRow, TargetCol + 3 * MaxSteps + i).Value = wshS.Cells(r, lngLVCol).Value
        Next i
        ' Enter number of steps
        wshT.Cells(TargetRow, 4).Value = lngSteps
        ' Optional: close source workbook
        wbkS.Close SaveChanges:=False
        Application.ScreenUpdating = True
    End Sub

    Hi Hans, can you help me out with this latest query? The above code works every time getting data from various E613 excel documents and putting data into my master excel document.

    What I am trying to do now is create a 3D model with the data using auto desk inventor using illogic which is vb.net

    I am trying to create the code in inventor to do actually the same thing as the code above, I have started to write the code and I have managed to get so far, the next step I need to do is the "if statement" between Hole position A and Hole Position B, then I'm not sure how to create the string to collect the data exactly the same way.

    Below is the code I have managed so far and this works every time getting the data from all my E613 docs.

    Would you know how to do the rest of the code for me?

    for info, how inventor works, I have created parameters in my 3d model, then the code gets the data from the E613 document I select and links it to the parameters. There is help for each of these excel snippets of code: http://help.autodesk.com/view/INVNTOR/2014/ENU/?guid=GUID-00F9D915-B53A-46BF-AAAC-3535F9FD9970

    Can you help

    Best regards

    hmk999

    New code below

    'set excel name string
    oExcelName = "C:\My Documents\Core & Clamps Development\Development_hmk\" & Core_Schedule & ".xls"

    'Write to Core_Circle parameter from the cell called Leg_Ctrs in the excel
    Core_Circle = GoExcel.CellValue(oExcelName, "Drawing", "Core_Circle")
    Leg_Centres = GoExcel.CellValue(oExcelName, "Drawing", "Leg_Ctrs")
    HOW = GoExcel.CellValue(oExcelName, "Drawing", "HOW")
    Number_of_Holes = GoExcel.CellValue(oExcelName, "Drawing", "No_of_Holes")
    Hole_Diameter = GoExcel.CellValue(oExcelName, "Drawing", "Hole_Diameter")
    Hole_Position_A = GoExcel.CellValue(oExcelName, "Drawing", "Hole_Position_A")


    • Edited by hmk999 Tuesday, January 21, 2014 8:21 PM spelling mistake
    Tuesday, January 21, 2014 8:19 PM
  • Sorry, I don't have the slightest idea what you're doing. I don't know anything about iLogic or VB.Net.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, January 21, 2014 8:46 PM
  • OK Hans thanks for coming back to me.

    I notice one of the topics at eileenslounge covers VBA/VA/.NET

    do you think it would be worth me trying to get some help on there? step by step, as I did with you in creating the original code.

    Thanks

    hmk999

    Tuesday, January 21, 2014 9:12 PM
  • It's worth a try - there are other Excel and VBA experts active there.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, January 21, 2014 10:37 PM