Changing Excel Cell Data

[Welshgasman]

The variables will not be filled UNTIL you execute that line?
When you stop on a line IT HAS NOT BEEN EXECUTED!


I am not even convinced your loop is correct, aren't you overwriting sheet 1 all the time?

[DMT Dave]

Ahh thanks WGM, would kindly copy and paste a correct method, i think its my not understanding !!!!!

much appreciated

[Micron]

Nor is
"[Name] Like ""*" & myCust & "*""") in post 15 what I posted: "[Name] Like '*" & c3 & "*'"

What about post 6? Why are you not doing this concatenation in Excel??

Do yourself (and anyone trying to follow) a favour and don't create so many unnecessary variables. The do have their place and function, but
a1 = c3 requires more examination than
a1 = xlsht2.Cells(i, 3).Value 'DELTO

You don't need .Value either as it's the default property, but that's of no concern. It's just makes every reference longer to write and read.

EDIT - does your code compile? It should not if you're using Option Explicit.
I'm adding to the edit for a bit - as I find stuff. Here's another one:
Dim myRow As Integer,
myRow = "1" <<will raise a run time error

- now I see that you used my suggestion in a 2nd set of lookups, but not the first set
- I have tested that syntax and it works
- your DLookups won't work if the field you're looking up is a lookup field in the table. Is that the case?
- Sticking with the code as a troubleshooting exercise, not because it's the best way, I put

MFG

SL

DELTO

END USER

ITEM TYPE

in a sheet (because I don't have the data) ran the modified code (because I don't have the form, table, etc.) and got

MFG & END USER

SL

DELTO

END USER

ITEM TYPE

So your code can work. If you cannot do this in Excel and cannot figure out your issue, then you'll have to make your db and spreadsheets available. Beyond proving that it can work, that's all I can contribute with what I have to work with.

[Welshgasman]

TBH I am still not sure what you are trying to achieve, nor why your dlookups do not work.

[Welshgasman]

Would just putting actual formulae in sheet 1 suffice?

[Welshgasman]

I would have approached it more like this?

Sub CopyData()
Dim iLastRow As Integer, iRow As Integer

iLastRow = Sheets("Kindle").UsedRange.Rows.Count
'Debug.Print iLastRow
For iRow = 1 To iLastRow
    Sheets("Sheet3").Range("A" & 6 + iRow).Value = Sheets("Kindle").Range("A" & iRow)
    Sheets("Sheet3").Range("B" & 6 + iRow).Value = Sheets("Kindle").Range("B" & iRow) & "-" & Sheets("Kindle").Range("C" & iRow)

Next
End Sub

Now I have done that all in Excel, which again is how I would approach getting it working, THEN modify for Access VBA.

[DMT Dave]

thank you guy's totally appreciated, I can study your comments and make some changes according to how you have advised

Thank you

[DMT Dave]

Wow, thank you WGM and Micron for your input, I have to proud of this adjustment and very shortened code, all works great....

Here it is

Dim srcPath As String, srcFile As StringDim xl As Excel.Application, xlsht As Excel.Worksheet, xlsht2 As Excel.Worksheet, xlWrkBk As Excel.Workbook
Dim iOpt As Integer, iLastRow As Integer, iRow As Integer


srcPath = "T:\DMT Ltd\XL Files\"
srcFile = "New Items.xlsx"
    
iOpt = InputBox(Chr(149) & " " & "0" & " " & Chr(149) & " ABORT THESE OPTIONS" & vbNewLine & vbNewLine & _
Chr(149) & " " & "1" & " " & Chr(149) & " OPEN PASTE FILE" & vbNewLine & vbNewLine & _
Chr(149) & " " & "2" & " " & Chr(149) & " TRANSFER SH2 To SH1", "ENTER OPTION")


Select Case iOpt
Case 0
    DoCmd.CancelEvent
Case 1
    Set xl = CreateObject("Excel.Application")
    xl.Visible = True
    xl.Workbooks.Open srcPath & srcFile, True, False
Case 2
    Set xl = CreateObject("Excel.Application")
    Set xlWrkBk = GetObject(srcPath & srcFile)
    Set xlsht = xlWrkBk.Worksheets(1)
    Set xlsht2 = xlWrkBk.Worksheets(2)
        iLastRow = xlsht2.UsedRange.Rows.Count
            For iRow = 1 To iLastRow
            xlsht.Range("E" & 5 + iRow).Value = xlsht2.Range("A" & iRow) & "-" & xlsht2.Range("E" & iRow)
            xlsht.Range("B" & 5 + iRow).Value = xlsht2.Range("C" & iRow)
            xlsht.Range("F" & 5 + iRow).Value = xlsht2.Range("B" & iRow)
            xlsht.Range("G" & 5 + iRow).Value = xlsht2.Range("F" & iRow)
            Next iRow
End Select


xl.Quit
Set xl = Nothing
Set xlWrkBk = Nothing
Set xlsht = Nothing

[Micron]

What event calls this code?
JMO but I would have made a form with custom buttons to avoid forcing user to input a value. Button captions would not need explaining & just one click?
Or an option frame (2 clicks) & maybe have that on the form that calls your code?

If user presses Cancel, you will raise an error - can't pass a zls to an integer. You will also find that if event is cancelled, your Set something = Nothing will also raise an error. Why not just exit sub if 0 instead of event cancel?

Maybe you're just rushing to come back & post and that's understandable. If not, when you think you have it right you should spend some time testing various inputs and scenarios.

[Micron]

Changes to code I'd make if keeping the input box:

Dim varOpt As Variant

varOpt = InputBox(Chr(149) & " " & "0" & " " & Chr(149) & " ABORT THESE OPTIONS" & vbNewLine & vbNewLine & _
Chr(149) & " " & "1" & " " & Chr(149) & " OPEN PASTE FILE" & vbNewLine & vbNewLine & _
Chr(149) & " " & "2" & " " & Chr(149) & " TRANSFER SH2 To SH1", "ENTER OPTION")

If varOpt = "" Or varOpt = 0 Then
   Exit Sub
Else
   Set xl = CreateObject("Excel.Application")
   
Select Case varOpt
''   Case 0
''      DoCmd.CancelEvent
   Case 1
      ''Set xl = CreateObject("Excel.Application")
      xl.Visible = True
      xl.Workbooks.Open srcPath & srcFile, True, False
   Case 2
      ''Set xl = CreateObject("Excel.Application")
      Set xlWrkBk = GetObject(srcPath & srcFile)
      Set xlsht = xlWrkBk.Worksheets(1)
      Set xlsht2 = xlWrkBk.Worksheets(2)
      iLastRow = xlsht2.UsedRange.Rows.Count
      For iRow = 1 To iLastRow
         xlsht.Range("E" & 5 + iRow).Value = xlsht2.Range("A" & iRow) & "-" & xlsht2.Range("E" & iRow)
         xlsht.Range("B" & 5 + iRow).Value = xlsht2.Range("C" & iRow)
         xlsht.Range("F" & 5 + iRow).Value = xlsht2.Range("B" & iRow)
         xlsht.Range("G" & 5 + iRow).Value = xlsht2.Range("F" & iRow)
      Next iRow
   End Select
End If

[Micron]

Last musing before I turn on the game:

Option frame option

Private Sub cmdOK_Click()
If Me.Frame0 = 0 Then
   Exit Sub
Else
   dmtDave3 Me.Frame0
End If

End Sub

Then you pass 1 or 2 to the sub and act on that, else just exit the click event. So no variable for the input, no input box code, no need to handle any of the 5 possibilities for the input box.

why
("E" & 5 + iRow) and not just "E5" ? Stuck on using .Value, I guess?

[DMT Dave]

Thanks Micron, I am going to change as suggested, particularly the input option

Will try your adjustments

Kindest

[DMT Dave]

Ahh right, varOpt will allow a user to input nothing at all or 0 to abort, and exit sub

The reason i adapted to doCmd.CancelEvent is to prevent a debug window but I understand why exit sub is better, never used me.Frame option so i will do some work on it but the goal is there apart from cleaning the code to your suggestion

Thank you