Macro for importing data from files

[Katla]

Hello, I am new to this forum and also new to VBA, although I have been able to get a couple of macros working.

What I am trying to do, is to import values from a number of files in a folder (market surveys) where all the work sheet names and cell formats are the same and combine them in one sheet. I have found this formula which works fine, except I want it to insert a blank cell if there is no data in the found cell in the file, and I want it to paste the values only. I am using Professional 2000, if it makes a difference.
Thank you in advance.


Sub runonalltotal()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "C:\Documents"
.FileType = msoFileTypeExcelWorkbooks
'Optional filter with wildcard
'.Filename = "Book*.xls"
If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through all
'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)


With ThisWorkbook.Sheets(1)
wbResults.Sheets("Sheet 1").Range("I54:I54").Copy _
Destination:=.Cells(2, .Columns.Count).End(xlToLeft)(1, 2)
Application.CutCopyMode = False
End With


wbResults.Close SaveChanges:=False
Next lCount
End If
End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

 

[machopicho]

Hello, I am new to this forum and also new to VBA, although I have been able to get a couple of macros working.

What I am trying to do, is to import values from a number of files in a folder (market surveys) where all the work sheet names and cell formats are the same and combine them in one sheet. I have found this formula which works fine, except I want it to insert a blank cell if there is no data in the found cell in the file, and I want it to paste the values only. I am using Professional 2000, if it makes a difference.
Thank you in advance.

You have to modify this piece of code:

.... 
With ThisWorkbook.Sheets(1)
wbResults.Sheets("Sheet 1").Range("I54:I54").Copy _
Destination:=.Cells(2, .Columns.Count).End(xlToLeft)(1, 2)
Application.CutCopyMode = False
End With
...

 

[Katla]

Thank you for your reply, that was fast! I did figure that out though, but the changes I tried did not work. Do you have any suggestions to what I could change it to?

 

[machopicho]

Show me your changes here, please.

 

[Katla]

Well, I only thought of:

With ThisWorkbook.Sheets(1)
wbResults.Sheets("Info on distributor & market").Range("B4:B4"). PasteSpecial xlValues .Copy _
Destination:=.Cells(1, .Columns.Count).End(xlToLeft)(1, 2)
End With

But again, this doesn't work for me. Also I have no idea of how to make the macro insert a blank cell if there is no value in the cell in the file.

 

[machopicho]

1) Is the sheet's name right?
2) why do you need to insert a blank cell if in cells of the file there is no value? If cell hasn't value automatically is a blanks cell...correct?

Be careful:

wbResults.Sheets("Info on distributor & market").[COLOR=#ff0000]Range("B4")[/COLOR].PasteSpecial xlValues .Copy _
Destination:=.Cells(1, .Columns.Count).End(xlToLeft)(1, 2)

 

[Katla]

1) Yes. As I said, the code works fine (just typed in a random sheet name at first) It is the modifications that do not work.

2) Well, I want each value returned from the files to be in a column for itself, to be compared with other values from the files (I will just repeat the code then). If there is no data in the selected cell however, it is because the person filling out the survey made a mistake, and the code should therefore paste a blank cell, otherwise the columns do not match with the following data. This will make it easy to look in the file and figure out what value should be typed. But the code only pastes the values from the cells that have values, so I will not see the error if it is there.

The code still doesn't work for the pastevalues.

 

[machopicho]

It's ok!
you don't have to copy a blank cell but you have to insert a blank cell where the data in imported column doesn't corresponde...right?
It's a problem explaine to you what you should make... can you give me your excel file?
Maybe i will have idea how the sheet is set....
You can upload your file on hosting site...

 

[Katla]

I can't really upload the files, since they are somewhat confidential. But let me try to explain it better. All the files have the same format, and in one of the cells, B4, the person filling it in types their country, and in B5, they type their name. Some of them forget to fill in one of these cells sometimes. When I use the code previously posted, the data that all the people have posted in cell B4 loads nicely into my new worksheet, one in the column left of the other, but if the cell is empty, the value of the cell in the next file is put in its place. If I then I repeat the code for B5 in the row underneath, it doesn't fit with the right countries, since the previous row has been moved one column in the places where people have forgotten to fill it. Does that make sense?

What I need is to modify this code, so it either includes the value of an empty cell, or inserts an empty cell where the cell value is zero.

 

[machopicho]

Ok, but it's very difficult explaine.
You can use something like this:

If Sheets(origin).Range("B4") = "" Then
Sheets(destination).Range("B4").Insert Shift:=xlDown

Try adapting this code as you need...