Hi There,

I have a VBA macro from work which transfers our reports from one excel sheet to another and then updates itself to particular dates under each user name.

I am totally confused how it works... because it doesn't use any kind of link with the file. I have done VB 6.0 and am out of touch but if I remember correctly you can use an OLEDB connection for it. But in this case I don't see any. The code is below.

Any explanation would be much appreciated

Cheers

Akki

Private Sub butGo_Click()
cwb = ActiveWorkbook.Name
cws = cmbMonth.Value
Worksheets(cws).Activate
Workbooks.Open Filename:=txtFile, ReadOnly:=True
nwb = ActiveWorkbook.Name
Workbooks(cwb).Activate
k = 0
Do While k < 13 And Cells(14 * k + 4, 1) <> ""
stafflook = Workbooks(cwb).Worksheets(cws).Cells(14 * k + 4, 1).Value
i = 4
Do While Workbooks(nwb).Worksheets("Rep Summary").Cells(i, 2).Value <> "" And Workbooks(nwb).Worksheets("Rep Summary").Cells(i, 2).Value <> stafflook
i = i + 1
Loop
repcampaign = Workbooks(nwb).Worksheets("Rep Summary").Cells(i, 5).Value
If repcampaign <> "In" And repcampaign <> "Se" And repcampaign <> "L" And repcampaign <> "T" Then
j = 0
Do While j < 3
Workbooks(cwb).Worksheets(cws).Cells(14 * k + 6 + j, cmbDay.Value + 2).Value = Workbooks(nwb).Worksheets("Rep Summary").Cells(i, 30 + j * 2).Value
Workbooks(cwb).Worksheets(cws).Cells(14 * k + 6 + j, cmbDay.Value + 52).Value = Workbooks(nwb).Worksheets("Rep Summary").Cells(i, 10 + j * 4).Value
j = j + 1
Loop
Workbooks(cwb).Worksheets(cws).Cells(14 * k + 6 + j, cmbDay.Value + 2).Value = Workbooks(nwb).Worksheets("Rep Summary").Cells(I, 45).Value
End If
k = k + 1
Loop
Workbooks(nwb).Close
End Sub

OLEDB? Wouldn't be needed for this.

basically this is grabbing the active workbook.
openening a new workbook

looping through the cells and updating values.
I can try to explain each step if you need to.

but basically it's a few simple loops that look for certain data, then copies data over from another sheet. No link are needed either..

cwb = ActiveWorkbook.Name <<< grabs the active workbooks name
cws = cmbMonth.Value <<< grabs the month from a dropdown combo
Worksheets(cws).Activate << activates the sheet
Workbooks.Open Filename:=txtFile, ReadOnly:=True <<< opens a workbook using the value of txtFile
nwb = ActiveWorkbook.Name <<< grabs the name of that workbook


there must be a form that opens?

Thanks soo much for the answer it becomes much clearer now. I guess when we use ODLEB would be for an access/sql database??
Yes it does open up a form where we need to specify the excel sheet.
It would be really great if you can run thro steps.

Your help is much appreciated.

Cheers

Akki


OLEDB? wouldnt be needed for this.

basically this is grabbing the active workbook.
openening a new workbook

looping thru the cells and updating values.
i can try to explain each step if you need to.

but basically its a few simple loops that look for certain data, then copies data over from another sheet. No link are needed either..

cwb = ActiveWorkbook.Name <<< grabs the active workbooks name
cws = cmbMonth.Value <<< grabs the month from a dropdown combo
Worksheets(cws).Activate << activates the sheet
Workbooks.Open Filename:=txtFile, ReadOnly:=True <<< opens a workbook using the value of txtFile
nwb = ActiveWorkbook.Name <<< grabs the name of that workbook


there must be a form that opens?

Private Sub butGo_Click()
cwb = ActiveWorkbook.Name
cws = cmbMonth.Value
Worksheets(cws).Activate
Workbooks.Open Filename:=txtFile, ReadOnly:=True
nwb = ActiveWorkbook.Name
Workbooks(cwb).Activate
k = 0
Do While k < 13 And Cells(14 * k + 4, 1) <> "" ' Basically its taking K * 14 + 4 and checking the cell if its empty (or K is less than 13)
stafflook = Workbooks(cwb).Worksheets(cws).Cells(14 * k + 4, 1).Value
I = 4 saving the value in stafflook
Do While Workbooks(nwb).Worksheets("Rep Summary").Cells(I, 2).Value <> "" And Workbooks(nwb).Worksheets(" And Workbooks(nwb).Worksheets(").Cells(I, 2).Value <> stafflook
'checking if the one cell is not empty and not = stafflook
'if neither add 1 to i
I = I + 1
Loop
repcampaign = Workbooks(nwb).Worksheets("Rep Summary").Cells(I, 5).Value
If repcampaign <> "In" And repcampaign <> "Se" And repcampaign <> "L" And repcampaign <> "T" Then if repcampaign isnt IN, SE, or L then go into the loop below
j = 0
Do While j < 3 Copying data over to the other sheet
Workbooks(cwb).Worksheets(cws).Cells(14 * k + 6 + j, cmbDay.Value + 2).Value = Workbooks(nwb).Worksheets("Rep Summary").Cells(I, 30 + j * 2).Value
Workbooks(cwb).Worksheets(cws).Cells(14 * k + 6 + j, cmbDay.Value + 52).Value = Workbooks(nwb).Worksheets("Rep Summary").Cells(I, 10 + j * 4).Value
j = j + 1
Loop
Workbooks(cwb).Worksheets(cws).Cells(14 * k + 6 + j, cmbDay.Value + 2).Value = Workbooks(nwb).Worksheets("Rep Summary").Cells(I, 45).Value
End If
k = k + 1
Loop
Workbooks(nwb).Close
End Sub




do that help?