Offset Row

[danny8890]

Hi,

I need to get the below to start from A2 rather than A1. I think i need to Offset the row but for the life of me cannot get this to work. copied the part of the VBA where i believe this needs to be added.

If someone could help that would be great

et wkb = appExcel.ActiveWorkbook
Set wks = wkb.Sheets(1)
wks.Activate
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate 'this is what i've tried'


appExcel.Application.Visible = False
  'Copy field items in mail folder.
  
For Each itm In fld.Items
intColumnCounter = 1




Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.Subject
intColumnCounter = intColumnCounter + 1


Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.Sender
intColumnCounter = intColumnCounter + 1


Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.SenderEmailAddress
intColumnCounter = intColumnCounter + 1


Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.Body
intColumnCounter = intColumnCounter + 1


Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.To
intColumnCounter = intColumnCounter + 1


Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.ReceivedTime




Next itm


 With wkb.Sheets(1)
    .Range("A:F").WrapText = False
    
End With


     wkb.Close 1
     If bXStarted Then
         xlApp.Quit
     End If


    ' Show summary message
        MsgBox "Finished" _

 

[Joe4]

Don't use "ActiveCell" in your coding, like this:

ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate 'this is what i've tried'

Rather, use a specific (or dynamic) range reference, i.e.

Range("A2")...

Also, you seldom need to select ranges in VBA to work with them.

 

[danny8890]

Thanks for your reply Jo,

So i've tried using range already but cannot see to get it working in this code. It works as its own sub. Could you help me getting into the code below

This works

Range("A1").Offset(RowOffSet:=1, ColumnOffset:=0).Select

Need to work in here

Sub ExportToExcel23()  On Error GoTo ErrHandler


Dim appExcel As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim rng As Excel.Range
Dim strSheet As String
Dim strPath As String
Dim intRowCounter As Integer
Dim intColumnCounter As Integer
Dim msg As Outlook.MailItem
Dim nms As Outlook.NameSpace
Dim fld As Outlook.MAPIFolder


Dim itm As Object
    strPath = "C:\Users\dr0808a\Desktop\UTA - Raw - Test.xlsm"
strSheet = strPath


Debug.Print strSheet
  'Select export folder
Set nms = Application.GetNamespace("MAPI")
Set fld = nms.PickFolder
  'Handle potential errors with Select Folder dialog box.
If fld Is Nothing Then


MsgBox "There are no mail messages to export"
Exit Sub




ElseIf fld.DefaultItemType <> olMailItem Then
MsgBox "There are no mail messages to export"
Exit Sub




ElseIf fld.Items.Count = 0 Then
MsgBox "There are no mail messages to export"
Exit Sub




End If
  'Open and activate Excel workbook.
Set appExcel = CreateObject("Excel.Application")
appExcel.Workbooks.Open (strSheet)
Set wkb = appExcel.ActiveWorkbook
Set wks = wkb.Sheets(1)
wks.Activate


appExcel.Application.Visible = False
  'Copy field items in mail folder.
For Each itm In fld.Items
intColumnCounter = 1




Set msg = itm
intRowCounter = intRowCounter + 1


Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.Subject
intColumnCounter = intColumnCounter + 1


Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.Sender
intColumnCounter = intColumnCounter + 1


Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.SenderEmailAddress
intColumnCounter = intColumnCounter + 1


Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.Body
intColumnCounter = intColumnCounter + 1


Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.To
intColumnCounter = intColumnCounter + 1


Set rng = wks.Cells(intRowCounter, intColumnCounter)
rng.Value = msg.ReceivedTime




Next itm


  With wkb.Sheets(1)
    .Range("A:F").WrapText = False
    
End With


     wkb.Close 1
     If bXStarted Then
         xlApp.Quit
     End If


' Show summary message
        MsgBox "Finished" _


Set appExcel = Nothing
Set wkb = Nothing
Set wks = Nothing
Set rng = Nothing
Set msg = Nothing
Set nms = Nothing
Set fld = Nothing
Set itm = Nothing


  Exit Sub
  
ErrHandler:  If Err.Number = 1004 Then
MsgBox strSheet & " doesn't exist"




Else




MsgBox Err.Number & "; Description: "




End If




Set appExcel = Nothing
Set wkb = Nothing
Set wks = Nothing
Set rng = Nothing
Set msg = Nothing
Set nms = Nothing
Set fld = Nothing
Set itm = Nothing


End Sub

 

[Joe4]

I would need to know a bit more about your project, namely:
- the structure of your data
- a clear, detailed explanation of exactly what it is you are trying to do

 

[danny8890]

I would need to know a bit more about your project, namely:
- the structure of your data
- a clear, detailed explanation of exactly what it is you are trying to do

Hi Joe, So this code is currently sat in outlook VBA and copys all emails from what folder i select into excel. This currently copy everything across fine but starts in A1. I need this to start in row A2.

 

[Joe4]

If I understand it correctly, it looks like you just need to initialize the value of intRowCounter before you For ... Next loop.
Currently, you are not setting it to anything, so it is zero initially. You are adding one before using it, so it starts at 1.
If you put a line like the following before the "For ..." line, I think it will do what you want.

intRowCounter = 1

 

[danny8890]

Exactly what i tried already, this works however only then copys one email doesnt loop and get all the emails, i added this straighter under the intcoloumncounter

 

[Joe4]

i added this straighter under the intcoloumncounter

That's the wrong place to put it. That is inside the loop, so it will reset in every loop, so EVERY record would be placed on row 2, so each successive loop would just overwrite the previous one, so you are just left with the last one.

You need to do what I suggested, and put it before the loop.

 

[danny8890]

That's the wrong place to put it. That is inside the loop, so it will reset in every loop, so EVERY record would be placed on row 2, so each successive loop would just overwrite the previous one, so you are just left with the last one.

You need to do what I suggested, and put it before the loop.

Amazing!, didn't read what you put properly works now... can't believe I was two lines off getting this to work...