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

Quote:

---

Originally Posted by StaticFX

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?