Help VBA - Importing Data

[hilio]

Hello,

I am new to VBA and I need some help.

I am trying to create an excel (2003) file, which enables me to import data using a macro and updates my file on a monthly basis.

I have attached 2 files as an example of what I am trying to achieve and the first excel file ( vba- example 1.xls ) contain the VBA code I have already done, and the second contains an example report.

I will try and explain what I am trying to do though.

Every month we have a report of the list of stock and the quantity they have been used. I need a macro which will loop though the report ( VBA-Example1 - Import.xls) and search for the item name from excel file 1 ( VBA- example 1.xls) with the 2nd one (VBA-Example1 - Import.xls) and then move the data from excel file 2 (VBA-Example1 - Import.xls) to the correct cell in the first excel file (vba- example 1.xls )

Code I have so far on VBA- example 1.xls

Dim FName As Variant
Dim i As Integer
Dim test As String

aMonth = (Month(Date))

MsgBox ("Please make sure the file is for month " & aMonth)

FName = Application.GetOpenFilename("Excel Files (*.xls),*.xls", , _
"Select Excel Data File")
If FName = False Then Exit Sub
Open FName For Input As #1


Application.ScreenUpdating = False
Workbooks.Open (FName)



Application.ScreenUpdating = True


Any help on how to achieve this will be greatly appreciated


Thank you

Hilio

[hardlife]

Private Sub Update_Click()


'Dim FName As Variant
'Dim i As Integer
'Dim test As String

'aMonth = (Month(Date))

'MsgBox ("Please make sure the file is for month " & aMonth)

FName = Application.GetOpenFilename("Excel Files (*.xls),*.xls", , _
"Select Excel Data File")
If FName = False Then Exit Sub
' Open FName For Input As #1


' Application.ScreenUpdating = False
' Workbooks.Open (FName)

' Application.ScreenUpdating = True

Set wkb = ThisWorkbook

'MsgBox ThisWorkbook.Sheets(1).Range("A4").Value

'PRODUCTFIND = Sheets(1).Range("A4").Value

'Set wkbExternal = Workbooks.Open("H:\9992\VBA-Example1 - Import.xls")
Set wkbExternal = Workbooks.Open(FName)
Set wksExternal = wkbExternal.Sheets(1) 'wkbExternal.Sheets("DATA")
wksExternal.Activate

wkb.Activate
Sheets(1).Range("A4").Activate
x = Sheets(1).Range("A4").Value
Do While ActiveCell <> ""
x = ActiveCell.Value
xaddress = ActiveCell.Address
MsgBox x

PRODUCTFIND = wkb.Sheets(1).Range(xaddress).Value

Set foundCellTyp = wksExternal.Cells.Find(What:=PRODUCTFIND, _
LookIn:=xlValues, LookAt:=xlWhole) '.Offset(0, 2).Value

'MsgBox PRODUCTFIND
If Not foundCellTyp Is Nothing Then
wkb.Sheets(1).Range(xaddress).Offset(0, 5).Value = foundCellTyp.Offset(0, 2).Value
MsgBox foundCellTyp.Offset(0, 2).Value
Else
MsgBox "this product was not found"
End If
ActiveCell.Offset(1, 0).Activate
Loop

End Sub


This is just midle of the road, wish You Good Luck,

Pavel Humenuk

[hardlife]

Private Sub Update_Click()


'Dim FName As Variant
'Dim i As Integer
'Dim test As String

'aMonth = (Month(Date))

'MsgBox ("Please make sure the file is for month " & aMonth)

FName = Application.GetOpenFilename("Excel Files (*.xls),*.xls", , _
"Select Excel Data File")
If FName = False Then Exit Sub
' Open FName For Input As #1


' Application.ScreenUpdating = False
' Workbooks.Open (FName)

' Application.ScreenUpdating = True

Set wkb = ThisWorkbook

'MsgBox ThisWorkbook.Sheets(1).Range("A4").Value

'PRODUCTFIND = Sheets(1).Range("A4").Value

'Set wkbExternal = Workbooks.Open("H:\9992\VBA-Example1 - Import.xls")
Set wkbExternal = Workbooks.Open(FName)
Set wksExternal = wkbExternal.Sheets(1) 'wkbExternal.Sheets("DATA")
wksExternal.Activate

wkb.Activate
Sheets(1).Range("A4").Activate
x = Sheets(1).Range("A4").Value
Do While ActiveCell <> ""
x = ActiveCell.Value
xaddress = ActiveCell.Address
MsgBox x

PRODUCTFIND = wkb.Sheets(1).Range(xaddress).Value

Set foundCellTyp = wksExternal.Cells.Find(What:=PRODUCTFIND, _
LookIn:=xlValues, LookAt:=xlWhole) '.Offset(0, 2).Value

'MsgBox PRODUCTFIND
If Not foundCellTyp Is Nothing Then
wkb.Sheets(1).Range(xaddress).Offset(0, 5).Value = foundCellTyp.Offset(0, 2).Value
MsgBox foundCellTyp.Offset(0, 2).Value
Else
MsgBox "this product was not found"
End If
ActiveCell.Offset(1, 0).Activate
Loop

End Sub


This is just midle of the road, good night,

Pavel Humenuk