Import data from individual employee monthly location sheets to onedepartment monthly location sheet

[anas.abdutty]

Hi
I have been tasked to upgrade our department's current employee location reporting system in excel. We have about 20 individual location sheets which each employee would fill out at the end of every month and submits a hard copy to the department administrator. The Dept. Admin then fills out the department location sheet based on the excel worksheets submitted to him. However, i would like to automate this process by adding a button next to each individual's name in the department location sheet, so that it will import his location details from his location sheet(which is emailed to the dept. admin by the individual) into the relevant cells in the department locationsheet. I have okay working knowledge with vba in access, but quite the beginner when it comes to excel. I would appreciate any help i get.

 

[isabelle]

hi,

where are the location details? on another sheet or on another workbook?
can you tell us more details?

isabelle

Le 2013-02-01 06:35, (e-mail address removed) a écrit :

Hi
I have been tasked to upgrade our department's current employee location reporting system in excel.

We have about 20 individual location sheets which each employee would
fill out at the end of every month

and submits a hard copy to the department administrator. The Dept. Admin
then fills out the department location

sheet based on the excel worksheets submitted to him. However, i would
like to automate this process by

adding a button next to each individual's name in the department
location sheet, so that it will import his location details

from his location sheet(which is emailed to the dept. admin by the
individual) into the relevant cells in the department location sheet.

I have okay working knowledge with vba in access, but quite the
beginner when it comes to excel. I would appreciate any help i get.

 

[anas.abdutty]

Thank you for your reply. These are seperate workbooks. Ill try and breakdown what I need to do:
1.Open the destination workbook
2.Run VBA code by clicking button adjascent to an individuals name which does the following.
Opens file browser to let me select the Source workbook.
Imports cell values from source workbook to destination workbook( E.g. I4:I20 to A3:A19)
Hope that helps. Thanks

 

[isabelle]

hi,

if it is possible to establish a link between the name into the cell and
the file name it is possible to read the information without opening the
file

isabelle

Le 2013-02-02 02:04, (e-mail address removed) a écrit :

 

[anas.abdutty]

Thank you for your reply. These worksheets will be submitted to the department admin by employees either via email or flash memory. So they will have to be browsed into and opened. Keeping my above posts in mind, i would alsolike to point to you that for each employee row in the main report the department admin will need to import relevant cell values from the related worksheet he gets from the individual. E.g. $F3:$AJ3 values to $L6:$L36. Couldyou please post some sort of an example code block that will serve this purpose. Thanks

 

[isabelle]

voilà,

Sub test1()
Dim vPath As String
Dim vFile As String
Dim vSheet As String

vPath = "C:\Users\isabelle\Documents\"
vFile = "MyFile.xls"
vSheet = "Sheet1"

'$F3:$AJ3 values to $L6:$L36

For i = 6 To 36
Cells(i, 12) = ExecuteExcel4Macro("'" & vPath & "[" & vFile & "]" &
vSheet & "'!R" & 3 & "C" & i & "")
Next
End Sub

isabelle

Le 2013-02-02 13:02, (e-mail address removed) a écrit :

Thank you for your reply. These worksheets will be submitted to the department admin by employees either via email or flash memory.

So they will have to be browsed into and opened. Keeping my above
posts in mind,

i would also like to point to you that for each employee row in the
main report the department admin will need to import relevant cell values

from the related worksheet he gets from the individual. E.g. $F3:$AJ3
values to $L6:$L36.

Could you please post some sort of an example code block that will serve
this purpose. Thanks

 

[anas.abdutty]

Thanks for the code but it pops the file browser the number of times the loop runs. Could you amend that?

 

[isabelle]

did you adapt the pathname, the filename and the sheet name to your needs?

isabelle

Le 2013-02-03 14:28, (e-mail address removed) a écrit :

 

[anas.abdutty]

yes i did, however i also had to amend the code further because i actually wanted to copy values from $L6:$L36 to $F3:$AJ3. Can you please take a look at the code below and tell me whats wrong and point me in the right direction. Thanks

Dim vPath As String
Dim vFile As String
Dim vSheet As String

vPath = "G:\Loc Sheets"
vFile = "Location Sheet v1_AA_30012013 - Test1.xlsx"
vSheet = "Location Sheet"

'$L6:$L36 to $F3:$AJ3 values

For i = 6 To 36
Cells(3, 6) = ExecuteExcel4Macro("'" & vPath & "[" & vFile & "]" & vSheet & "'!R" & 3 & "C" & i & "")
Next
End Sub

 

[isabelle]

ok here is the modification,

Sub test1()
Dim vPath As String
Dim vFile As String
Dim vSheet As String
Dim i As Integer

vPath = "G:\Loc Sheets"
vFile = "Location Sheet v1_AA_30012013 - Test1.xlsx"
vSheet = "Location Sheet"

'copy values from $L6:$L36 to $F3:$AJ3
For i = 6 To 36
Cells(3, i) = ExecuteExcel4Macro("'" & vPath & "[" & vFile & "]" &
vSheet & "'!R" & i & "C" & 12 & "")
Next
End Sub

isabelle

 

[anas.abdutty]

Thank you for your quick reply, I pasted your exact code block into my worksheet but it still opens the file browser the number of times the loop executes. I have no idea why this is happening. Any ideas please? Could it be something wrong with the ExecuteExcel4Macro function?

 

[isabelle]

I've never seen this behavior on the versions from 95 to 2010,
you work on which version?

isabelle

Le 2013-02-03 15:37, (e-mail address removed) a écrit :

Thank you for your quick reply, I pasted your exact code block into my worksheet but it still opens

the file browser the number of times the loop executes. I have no idea
why this is happening.

Any ideas please? Could it be something wrong with the
ExecuteExcel4Macro function?

 

[isabelle]

can you try this new version with Application.ScreenUpdating

Sub test2()
Dim vPath As String
Dim vFile As String
Dim vSheet As String
Dim i As Integer
Application.ScreenUpdating = False
vPath = "G:\Loc Sheets"
vFile = "Location Sheet v1_AA_30012013 - Test1.xlsx"
vSheet = "Location Sheet"

'copy values from $L6:$L36 to $F3:$AJ3
For i = 6 To 36
Cells(3, i) = ExecuteExcel4Macro("'" & vPath & "[" & vFile & "]" &
vSheet & "'!R" & i & "C" & 12 & "")
Next
Application.ScreenUpdating = True
End Sub

isabelle

Le 2013-02-03 16:08, isabelle a écrit :

 

[anas.abdutty]

Still the same. Thanks a lot for your help anyways. Much appreciated.

 

[anas.abdutty]

I have posted the final code below in case it proves helpful for someone else too. I finally got it to work with yours and some others(utter access excel forum) help. Adiós

Sub Import1()
Dim Dlg As FileDialog
Dim txtFilePath As String
Dim myfile As String
Set Dlg = Application.FileDialog(msoFileDialogFilePicker)
With Dlg
..Title = "Select the Dashboard"
..AllowMultiSelect = False
..Filters.Clear
If .Show = -1 Then
txtFilePath = .InitialFileName
Else
Exit Sub
End If
End With
myfile = Dlg.SelectedItems(1)

Dim wb As Workbook
Dim rngRange As Range
Dim wsDest As Worksheet
Dim wsSource As Worksheet
Dim counter As Integer
Set wb = Workbooks.Open(myfile)
Set wsSource = wb.Worksheets("Location Sheet")
Set wsDest = ThisWorkbook.Worksheets("MLR")
wsDest.Range("$F3:$AJ3").Value = ""
'copy values from $L6:$L36 to $F3:$AJ3
For counter = 6 To 36
Set curCell = wsDest.Cells(3, counter)
curCell.Value = wsSource.Cells(counter, 12).Value
Next counter
'close the source workbook
ActiveWorkbook.Close
End Sub