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?
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?
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
Ahh thanks WGM, would kindly copy and paste a correct method, i think its my not understanding !!!!!
much appreciated
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.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
TBH I am still not sure what you are trying to achieve, nor why your dlookups do not work.
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
Would just putting actual formulae in sheet 1 suffice?
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
I would have approached it more like this?
Now I have done that all in Excel, which again is how I would approach getting it working, THEN modify for Access VBA.Code: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
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
thank you guy's totally appreciated, I can study your comments and make some changes according to how you have advised
Thank you
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
Code: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
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.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
Changes to code I'd make if keeping the input box:
Code: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
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
Last musing before I turn on the game:
Option frame option
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.Code:Private Sub cmdOK_Click() If Me.Frame0 = 0 Then Exit Sub Else dmtDave3 Me.Frame0 End If End Sub
why
("E" & 5 + iRow) and not just "E5" ? Stuck on using .Value, I guess?
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
Thanks Micron, I am going to change as suggested, particularly the input option
Will try your adjustments
Kindest
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