Import Data Problem

[Stefan_Sand]

Hi,

i have this wonderful DataImportTool, but for a reason, i don´t understand, it doesn´t work anymore after altering the import parameters....
see attached file. I always get the errorcode for solution b, i do not want to test right now solution b, i only want to see if the altered code imports the new data anyway.
The error row with the code shows:
dblStep2 = 1 / parameter.Cells(9, 4) ' Solution für Case B

does someone have a clue?

Stefan

ps: Datenimport is the tool, and TestImpData1 is a testfile to import

[HansV]

What am I supposed to do?

[Stefan_Sand]

well, i only got out, that the import stops when, the number of x´s for case b is 0-- even if i have set case a...
and i don´t really know, if i adopted the code right to import the right columns.

[HansV]

If I click the Import button, I immediately get an error because a path does not exist.

Please describe the steps I have to take in order to test the code. Otherwise I won't be able to help you.

[Stefan_Sand]

ok, step by step:

in the parameter sheet i set a path, where all the sourcefiles are in.
in case a, all the sheets will be imported as the method is set in the code
in case b i have to list all the files in the sourcepath and then to set a flag (=x) which file i want to import.

i guess, there must be something wrong with the automatic calculation or 0 for the files to import in case b

at least, maybe i alterd the import code wrong for the data to import

stefan

[HansV]

The line that causes the error

Code: Select all

  dblStep2 = 1 / parameter.Cells(9, 4)      ' Solution für Case B

is above the line where you test whether the user wants case A or B:

Code: Select all

If ThisWorkbook.Worksheets("parameter").Cells(10, 4) = "A" Then

So this line will always be executed. If you only want it to be executed for case B, you must move it below

Code: Select all

ElseIf ThisWorkbook.Worksheets("parameter").Cells(10, 4) = "B" Then

[Stefan_Sand]

ok, thank you, hans,
now it imports the data. do i import the listed data in the right way?
soory for asking that again,
stefan

[HansV]

Please explain exactly what I have to do. Otherwise I won't help you.

[Stefan_Sand]

i want to import all the data of the source file of columns
Column
FkDatum 1
Rechn.nr 2
Techn.Platz Nr. 3
Equipmentnr 6
OWS-ID 7
Equipmenttext 8
ABC-Kennz. 11
Hauptmaterial 13
Materialnr. 15
Betrag 19
Ansch.Dat 21
Vertr.Beginn 22
Laufzeit 24
offene Raten 25
USERID 26

being imported, my question was, have i set my code in the right way to do this properly?
its the importing part in the module modAddData.
regards
stefan

[HansV]

You haven't told me in detail, step by step, what I have to do in order to import the file, so I cannot answer your question.

[Stefan_Sand]

ok, the question is how do i have to adopt my import routine to import all the data of the source files in a chosen filefolder, from columns
1,2,3,6,7,8,11,13,15,19,21,22,24,25,26 into the Konsolidation worksheet.

Stefan

[HansV]

Stefan, since you stubbornly refuse to provide the information I ask for, I cannot do anything for you. Sorry.

[Stefan_Sand]

Hans, i gave you both, the sourcefile with its structure and i uploaded the importfile with the routine data, i asked how should i adopt the code to import the data in the requested columns, what more do i have to ask?

[HansV]

You still haven't told me exactly which steps I should take to import the sample file you provided. If I simply click the Import button I immediately get an error message. So I presume I have to take some steps to prepare for the import. I have asked you several times what I have to do in order to import the file, and each time you simply ignore this request.

[Stefan_Sand]

ok,
first, you only have to set the number for case b in cells (9, 4) in the parameter sheet manually to 1 so the routine can start anyway, - alternatively set the calculation option in the exceloptions from manual to automatic and set and/or after listing the files in the set order set an x in column e for the file.
after this you can start the import.
What i asked for as a help was, how do i adopt the import code , because i think i have done something wrong with this alternation - > i want to import not all of the data in the sheet(s) - there are about 100 - 500 files, containing some data in the same order;
i want only the data in the columns i mentioned above, so i set the import routine to do so.
note, my solution is based on a former request of mine here in the forum, where you helped me to figure it out.

[HansV]

No, the code is not correct, it keeps on importing row 2 over and over again.
Change the code for case B starting at

Code: Select all

    Set wbk = Workbooks.Open(Filename:=strPath & strFile, AddToMRU:=False)   ' Open workbook

and ending at

Code: Select all

    wbk.Close SaveChanges:=False                                             ' Close workbook

to

Code: Select all

    Set wbk = Workbooks.Open(Filename:=strPath & strFile, AddToMRU:=False)   ' Open workbook
    Dim lngSourceRow As Long
    Dim lngLastRow As Long
    For Each wshSource In wbk.Worksheets                                     ' Loop through the worksheets
      lngLastRow = wshSource.Cells(Rows.Count, 1).End(xlUp).Row              ' Last used row
      For lngSourceRow = 2 To lngLastRow                                     ' Loop through rows
        lngTargetRow = lngTargetRow + 1                                      ' New row in target sheet
        wksDest.Cells(lngTargetRow, 1) = wshSource.Cells(lngSourceRow, 1)    ' xxx
        wksDest.Cells(lngTargetRow, 2) = wshSource.Cells(lngSourceRow, 2)    ' xxx
        wksDest.Cells(lngTargetRow, 3) = wshSource.Cells(lngSourceRow, 3)    ' xxx
        wksDest.Cells(lngTargetRow, 4) = wshSource.Cells(lngSourceRow, 6)    ' xxx
        wksDest.Cells(lngTargetRow, 5) = wshSource.Cells(lngSourceRow, 7)    ' xxx
        wksDest.Cells(lngTargetRow, 6) = wshSource.Cells(lngSourceRow, 8)    ' xxx
        wksDest.Cells(lngTargetRow, 7) = wshSource.Cells(lngSourceRow, 11)   ' xxx
        wksDest.Cells(lngTargetRow, 8) = wshSource.Cells(lngSourceRow, 13)   ' xxx
        wksDest.Cells(lngTargetRow, 9) = wshSource.Cells(lngSourceRow, 15)   ' xxx
        wksDest.Cells(lngTargetRow, 10) = wshSource.Cells(lngSourceRow, 19)  ' xxx
        wksDest.Cells(lngTargetRow, 11) = wshSource.Cells(lngSourceRow, 21)  ' xxx
        wksDest.Cells(lngTargetRow, 12) = wshSource.Cells(lngSourceRow, 22)  ' xxx
        wksDest.Cells(lngTargetRow, 13) = wshSource.Cells(lngSourceRow, 24)  ' xxx
        wksDest.Cells(lngTargetRow, 14) = wshSource.Cells(lngSourceRow, 25)  ' xxx
        wksDest.Cells(lngTargetRow, 15) = wshSource.Cells(lngSourceRow, 26)  ' xxx
      Next lngSourceRow
    Next wshSource
    wbk.Close SaveChanges:=False                                             ' Close workbook

[Stefan_Sand]

Hans, once again than you very much for your support; i adopted the code as described and it imports the data, but, for unknown reason for me there are several rows without any data between the data of the next imported workbook.

Stefan

[HansV]

It must be a peculiarity of your data files; if I import your sample data file repeatedly, there are no empty rows. Do the data files start with a lot of empty rows?

[Stefan_Sand]

No Hans, they look like the provided Data file to import, maybe i should start excel again. hm, i don´t know why it is like this[font=][/font]

ok, I found out my own error, it was at the start of the routine in Case A, where i should loop through rows, i looped through columns, now it works.

[Don Wells]

If I click the Import button, I immediately get an error because a path does not exist.

Hi Hans
    You certainly got farther on this than me. Where did you find the Import button? I was unable to locate it.