+ Reply to Thread
Results 1 to 14 of 14

Need to import Data from multiple Excel file in a Excel Table.

  1. #1
    Registered User
    Join Date
    06-25-2014
    Location
    Indore, India
    MS-Off Ver
    Office 2007
    Posts
    18

    Need to import Data from multiple Excel file in a Excel Table.

    Hi,

    I have lots of Excel files which containing data needed to be import in single Excel format.

    Is there any way to to import particular cell data to a given format.

    I have attached two sample files, 1st one contains Data & 2nd file contains excel format. (I have filed and marked those cells with similar colors in both files
    which cells data need to be import.)

    Please any one suggest what to do i have around 1500+ files from which data need to be import.

    any VB script which import data directly from selected folder.

    Thanks

    AatifSample Excel Format.xlsxSample - SIDHI 22019589.xls

  2. #2
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Need to import Data from multiple Excel file in a Excel Table.

    Hi Aatif
    Quote Originally Posted by aatif0712 View Post
    .....I have lots of Excel files which containing data needed to be import in single Excel format.
    Is there any way to to import particular cell data to a given format......
    _. There are many many ways to do this sort of thing in VBA

    _. It is not too clear exactly what you want.

    _. Here is one code that may help you get started.

    _ . For this code to work you must
    _1 ) copy the code to a Normal Module or a Worksheets Module in the Main Excel Format File. This File must be open
    _ 2) The Excel Data File to have its data imported must also be open, and IMPORTANTLY: it must be Active, with the sheet with data to be imported Active. ( So you must be looking on your screen at this file when you run the program )

    The code will for example take this File( I named SIDHI 12345678.xls )


    Attachment 428176


    And after running the code this :

    Using Excel 2007
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    R
    S
    T
    U
    V
    W
    X
    Y
    Z
    AA
    AB
    AC
    AD
    2
    SIDHI
    Nov 14
    22019589
    19.11.2014
    SIDHI 22019589.XLS
    24.88526
    81.67093
    24.8505
    81.66105
    HIGH LOSS REMOVE FOR REPLACING 4000MTR OFC
    3
    Sheet1

    _ .... will change to this:

    Using Excel 2007
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    R
    S
    T
    U
    V
    W
    X
    Y
    Z
    AA
    AB
    AC
    AD
    2
    SIDHI
    Nov 14
    22019589
    19.11.2014
    SIDHI 22019589.XLS
    24.88526
    81.67093
    24.8505
    81.66105
    HIGH LOSS REMOVE FOR REPLACING 4000MTR OFC
    3
    SIDHI
    Nov 14
    33333
    25.11.2014
    SIDHI 12345678.xls
    1234
    910
    5678
    1112
    Hallo Aatif. P-tang P-Tang OLE Wigwam Biscuit Barrel Du WTF Wolly Wop
    Sheet1

    Note also I chose to make two help columns which return this in the data File

    Attachment 428175

    _ . Here is the code that I have written for you:
    http://www.excelforum.com/developmen...ml#post4230719


    Alan
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  3. #3
    Registered User
    Join Date
    06-25-2014
    Location
    Indore, India
    MS-Off Ver
    Office 2007
    Posts
    18

    Re: Need to import Data from multiple Excel file in a Excel Table.

    Thanks Alan,

    This really help me a lot and save many hours of my team.....Just i need to open every file and press F5......

  4. #4
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Need to import Data from multiple Excel file in a Excel Table.

    Hi Aatif
    Quote Originally Posted by aatif0712 View Post
    Thanks Alan,
    This really help me a lot and save many hours of my team.....
    You are welcome
    Thanks for the feedback
    Alan

  5. #5
    Registered User
    Join Date
    06-25-2014
    Location
    Indore, India
    MS-Off Ver
    Office 2007
    Posts
    18

    Re: Need to import Data from multiple Excel file in a Excel Table.

    hi Alan,

    Can I use this code for different file, actually when I change the cells in coding, it will not jump on blank row and just overwrites the previous data. Pleasw suggect.

    Thanks

    Aatif...

  6. #6
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Need to import Data from multiple Excel file in a Excel Table.

    Hi Aatif,

    Working on the same ( „Before“ ) Screenshots I gave in Post #2,

    _ The new code i give You will result this time in this ( “After” ) output

    Using Excel 2007
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    R
    S
    T
    U
    V
    W
    X
    Y
    Z
    AA
    AB
    AC
    AD
    AE
    1
    Sampl
    Sample Excel Format.xlsm
    MEPT
    MonthName
    FTNo
    CauseCode
    PCode
    MDesc
    UOM
    Quantity
    GISApplicable
    FieldInput
    GISUpdated
    FIDate
    GISDate
    ReasonForPend
    Remarks
    FileName
    EntrySignum
    EntryDate
    SoM
    RouteNameFrom
    RouteNameTo
    SpanFrom
    SpanTo
    LatFrom
    LongFrom
    LatTo
    LongTo
    WorkDone
    SoilStrata
    2
    SIDHI
    Nov 14
    33333
    25.11.2014
    SIDHI 12345678.xls
    1234
    910
    5678
    1112
    Hallo Aatif. P-tang P-Tang OLE Wigwam Biscuit Barrel Du WTF Wolly Wop
    Sheet1

    Code modified:

    Please Login or Register  to view this content.
    Last edited by Doc.AElstein; 12-18-2015 at 04:10 PM. Reason: Correct missing +1 in last code line

  7. #7
    Registered User
    Join Date
    06-25-2014
    Location
    Indore, India
    MS-Off Ver
    Office 2007
    Posts
    18

    Re: Need to import Data from multiple Excel file in a Excel Table.

    Hi Alan,

    Thanks for new code, I have attached sample format for your reference.

    Source file is "CG_ABHN_001-25_ABHANPUR.xlsx" (data need to export is highlighted with green color), and macro file with code attached.

    I am getting error when i add "E36" cell and export to column "AC". "AC" Coloumn is already filled with "Latitude" as shown in attached excel "Sample Excel Format.xlsm".

    Sample Excel Format.xlsm

    CG_ABHN_001-25_ABHANPUR.xlsx

    Thanks
    Aatif.....

  8. #8
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Need to import Data from multiple Excel file in a Excel Table.

    Hi aatif
    Quote Originally Posted by aatif0712 View Post
    ......

    I am getting error when i add "E36" cell and export to column "AC". "AC" Coloumn is already filled with "Latitude" as shown in attached .........

    I see the problem straight away. I have made a mistake in my programs. Sorry.


    Brief Explanation:

    The 1 Dimensional Array being used for the Output Values, arrOut() is dimensioned thus

    _________=0, 1, 2, 3, ....... ( up to 28 for your latest Files )

    _- I chose to start at 0 simply for convenience and consistance as the “internally“ created Arrays
    ______arrSelectedCells()
    and
    ¬¬¬¬____arrFormattedCellsLtr()
    are “made” by default by VBA to have that convention.

    Hence the Upper limit given by
    ___UBound(arrOut())
    in my codes, will return for your latest Files 28. This is one less than the total number of elements in the Output Array.

    So the last code line in my codes is wrong.

    The Range size ( in counted Array Elements ) required should be equal to
    ___( UBound(arrOut()) + 1)

    This is the size to which the Range to which the Resize property is applied should be resized.

    Hence my Final Lines should read

    Let wsFormats.Cells((lr + 1), 1).Resize(1, (UBound(arrOut()) + 1)).Value = arrOut()

    And

    Let wsFormats.Cells((1 + 1), 1).Resize(1, (UBound(arrOut()) +1).Value = arrOut()

    _ .......................................

    I have modified my posted code above accordingly. And asked for my code posted earlier to be modified.

    Apologies for the Mistakes

    Alan

  9. #9
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Need to import Data from multiple Excel file in a Excel Table.

    Quote Originally Posted by aatif0712 View Post
    .......

    I am getting error when i add "E36" cell and export to column "AC". "AC" Coloumn is already filled with "Latitude" as shown in attached ........
    Hi Aatif,
    I see the problem straight away. I have made a mistake in my programs. Sorry.


    Brief Explanation:


    The 1 Dimensional Array being used for the Output Values, arrOut() is dimensioned thus

    _________=0, 1, 2, 3, ....... ( up to 28 for your latest Files )

    _- I chose to start at 0 simply for convenience and consistance as the “internally“ created Arrays
    ______arrSelectedCells()
    and
    ¬¬¬¬____arrFormattedCellsLtr()
    are “made” by default by VBA to have that convention.

    Hence the Upper limit given by
    ___UBound(arrOut())
    in my codes, will return for your latest Files 28. This is one less than the total number of elements in the Output Array.

    So the last code line in my codes is wrong. The Range size ( in counted Array Elements ) required should be equal to
    ___( UBound(arrOut()) + 1)

    This is the size to which the Range to which the Resize property is applied should be resized.

    Hence my Final Lines should read

    Let wsFormats.Cells((lr + 1), 1).Resize(1, (UBound(arrOut()) + 1)).Value = arrOut()

    And

    Let wsFormats.Cells((1 + 1), 1).Resize(1, (UBound(arrOut()) +1).Value = arrOut()

    _ .......................................

    I have modified my posted code above accordingly. And asked for my code posted earlier to be modified.

    Apologies for the Mistakes

    Alan

  10. #10
    Registered User
    Join Date
    06-25-2014
    Location
    Indore, India
    MS-Off Ver
    Office 2007
    Posts
    18

    Re: Need to import Data from multiple Excel file in a Excel Table.

    Dear Alan,

    Sorry for delay response, i was out of town for 3-4 day. Thanks for modified code. But now it stuck on "AD" showing run-time error "9". I need 32 columns to be import. Please suggest the solution. I am using this below mentioned Code(Old Code). Above code(Modified code) overwrites previously import data.

    Please go through attached files in my previous post.

    CODE:-

    Please Login or Register  to view this content.
    Thanks
    Aatif

  11. #11
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Need to import Data from multiple Excel file in a Excel Table.

    Hi aatif,

    I was able to repeat the error. I see the Problem.
    The original code was written for a File where you had Headings in the First Row.
    The codes, as i have written them, all require finding of the last Heading column, lc.
    In the original code, this last column, lc, was determined by looking for the last heading in Row 1 using this line

    Please Login or Register  to view this content.
    Your new file has headings in column 2. You need therefore to get lc in a different way.

    There are many ways to do this.

    For example:
    _ (i) Simply look in the second row with this Coode line
    Please Login or Register  to view this content.
    _(ii) Replace that code line with this which determines the last column in the whole sheet.
    Please Login or Register  to view this content.
    Note it would be wise only to use (ii) if you have nothing in the sheet to the right of your Input Table. Should you have anything then this code line will give you the last column in which anything in the sheet is

    ( (iii) Another possibility could be to determine the required Output Array size based on the last Address letter in the Array
    arrFormattedCellsLtr()
    ____.. but this would require that you always choose to have the column Letter of the column furthest to the right as the last entry in that Array )

    I checked the codes with the above modifications on your Files and they run now without error.

    Here is part of your Input File:


    Excel 2007
    Row\Col
    C
    D
    E
    2
    Item
    Units
    RTL GSM
    3
    Height of corner C1
    Mtrs
    0
    4
    Distance of corner C1 from tower
    Mtrs
    6
    5
    Height of corner C2
    Mtrs
    0
    6
    Distance of corner C2 from tower
    Mtrs
    4
    7
    Height of corner C3
    Mtrs
    0
    8
    Distance of corner C3 from tower
    Mtrs
    5
    9
    Height of corner C4
    Mtrs
    0
    10
    Distance of corner C4 from tower
    Mtrs
    6
    11
    Building B1
    12
    Lattitude
    21.05136
    13
    Longitude
    81.7433
    Sheet: Field Data


    After running the modified code 2 times, part of the Sample File ( Sheet1 ) ( which originally has no data entries ) changes to look like this:


    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    R
    S
    T
    U
    V
    W
    X
    Y
    Z
    AA
    AB
    AC
    AD
    AE
    AF
    AG
    AH
    2
    Lattitude
    Longitude
    Building Height AGL, in m
    No. of Floors
    Distance from Tower
    Azimuth
    Lattitude
    Longitude
    Building Height AGL, in m
    No. of Floors
    Distance from Tower
    Azimuth
    Lattitude
    Longitude
    Building Height AGL, in m
    No. of Floors
    Distance from Tower
    Azimuth
    Lattitude
    Longitude
    Building Height AGL, in m
    No. of Floors
    Distance from Tower
    Azimuth
    3
    CG_ABHN_001-25_ABHANPUR.xlsx
    0
    6
    0
    4
    0
    5
    0
    6
    21.0514
    81.7433
    4
    1
    23
    25
    21.0512
    81.7437
    4
    1
    52
    85
    21.0508
    81.74343
    4
    1
    46
    150
    21.05137
    81.74306
    4
    1
    26
    325
    4
    CG_ABHN_001-25_ABHANPUR.xlsx
    0
    6
    0
    4
    0
    5
    0
    6
    21.0514
    81.7433
    4
    1
    23
    25
    21.0512
    81.7437
    4
    1
    52
    85
    21.0508
    81.74343
    4
    1
    46
    150
    21.05137
    81.74306
    4
    1
    26
    325
    Sheet: Sheet1




    Hope that helps
    Alan
    Last edited by Doc.AElstein; 12-22-2015 at 07:30 PM. Reason: Testies.... I can say that here

  12. #12
    Registered User
    Join Date
    06-25-2014
    Location
    Indore, India
    MS-Off Ver
    Office 2007
    Posts
    18

    Re: Need to import Data from multiple Excel file in a Excel Table.

    Thanks Alan this work fine. Thanks a lot for your precious time.
    This help me a lot and save time of team.

    Thanks
    Aatif.

  13. #13
    Registered User
    Join Date
    06-25-2014
    Location
    Indore, India
    MS-Off Ver
    Office 2007
    Posts
    18

    Re: Need to import Data from multiple Excel file in a Excel Table.

    Thanks Alan this work fine. Thanks a lot for your precious time.
    This help me a lot and save time of team.

    Thanks
    Aatif.

  14. #14
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Need to import Data from multiple Excel file in a Excel Table.

    Quote Originally Posted by aatif0712 View Post
    .....
    This help me a lot and save time of team.

    Thanks
    Aatif.
    You are welcome
    Thanks for the Feedback
    Alan

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Import Text File to Access Table using Excel VBA
    By balajismith1992 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-28-2015, 09:34 PM
  2. [SOLVED] Import Multiple Excel File to MS Access at once
    By Mohanmoni in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-28-2014, 10:38 AM
  3. Import multiple *.tsv files into one excel file
    By bing.pascual in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-26-2013, 01:57 AM
  4. Import Text File to Access Table using Excel VBA
    By curiosity1990 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-07-2013, 09:43 AM
  5. Import data from Excel file into filtered cells in another Excel file
    By Rebecca12 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-14-2012, 04:05 PM
  6. Import data from multiple Excel files to 1 Excel file.
    By agengler11 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-10-2012, 04:23 AM
  7. How to import data from multiple Excel files to one Excel file
    By rafeemd in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-15-2011, 12:47 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1