Excel Sheet Updadting

[DMT Dave]

Hi Guyt's, where am i going wrong here please ?



I am trying to update target file and sheet on the next avalable row in column A

from a selected page from source file, so immediate window is giving me correct rows and amout of data but not transferring to target file and sheet

also when i quit and set all instanaces to nothing, the sheets are wiped clean (have back ups)


hence commeneted out

Dim i As Integer, iAddRecs As Integer, iRecsNext As Integer, iNum As Integer, x As Integer, intLR As Integer, intLRAll As Integer, intLR2 As Integer, iNextRow As IntegerDim rs As DAO.Recordset
Dim strCode As String, strResult() As String, strNum As String, strFind As String, strNewRec As String, strOrgCode As String
Dim xlAP As Object, xlSourceWB As EXCEL.Workbook, xlTargetWB As EXCEL.Workbook, xlSourceSHT As EXCEL.Worksheet, xlTargetSHT As EXCEL.Worksheet, fndRng As EXCEL.Range
Dim xlAP2 As Object, xlWB2 As EXCEL.Workbook, xlSHT3 As EXCEL.Worksheet, fndRng2 As EXCEL.Range, rngTo As Range, rngFrom As Range
Dim xl As Object, xl2 As Object
Dim strPath As String, strSourceFile As String, strTargetFile As String, strData As String
Dim intPage As Integer, iSourceLR As Integer, iTargetLR As Integer, iTargetNR As Integer
Dim varPage As Variant


varPage = InputBox("Enter Page A-Z ?" & vbCrLf & vbCrLf & _
        "Leave Blank To Abort", "ENTER PAGE")


Select Case varPage
Case Is = ""
    Exit Sub
Case Else


    strPath = "C:\Users\davem\Desktop\"
    strTargetFile = "Target File.xlsx"
    strSourceFile = "Source File.xlsx"
    
    Set xlAP = CreateObject("Excel.Application")
    Set xlAP2 = CreateObject("Excel.Application")


    Set xlSourceWB = GetObject(strPath & strSourceFile)
    Set xlTargetWB = GetObject(strPath & strTargetFile)
    
    Set xlSourceSHT = xlSourceWB.Worksheets(varPage)
    Set xlTargetSHT = xlTargetWB.Worksheets("Customers")
        
    xlAP.Visible = False
    xlAP2.Visible = False
    
    With xlTargetSHT
    
    iSourceLR = xlSourceSHT.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row ' Find Last Row With Data
    iTargetLR = xlTargetSHT.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row ' Find Last Row With Data
    
    iTargetNR = iTargetLR + 1 ' Next Row To Append Data
    
        Debug.Print xlSourceSHT.Name & " - " & iSourceLR & " Total Rows" & vbCrLf & vbCrLf & _
                        xlTargetSHT.Name & " - " & "To Go From Row " & iTargetNR
            
        For i = 1 To iSourceLR
                    
            xlTargetSHT.Cells(iTargetNR, 1) = xlSourceSHT.Cells(2 + iSourceLR, 1)
            
        iSourceLR = iSourceLR + 1
        
        Next i
        
    End With
    
    
    xlSourceWB.Close
    'xlTargetWB.Close True
           
  '  Set xlTargetSHT = Nothing
  '  Set xlSourceSHT = Nothing
    
   ' Set xlSourceWB = Nothing
   ' Set xlTargetWB = Nothing
    
   ' xlAP.Quit
   ' xlAP2.Quit
    
   ' Set xlAP = Nothing
   ' Set xlAP2 = Nothing

[Welshgasman]

You do not need two Excel applications running? Could that be the cause, as each will not know about the other?

AGAIN!!! walking your code would likely show that?

[DMT Dave]

hi WGM, yes i will set some break points and see your note re pening 2 apps, just thoght 2 seperate workbooks would need to apps setting and as far as closing them, terminating in reverse order of setting but marked thme out as it was clearing a full wb

i will set some break points and see whats happening, i know i have the correct records via debug.print

[DMT Dave]

Hi WGM, so as far as walking code, my thought is rule out running 2 apps and start break points before any outputs, so the issue is the file location, i am reading this correct am I ?

I will double check location (path) is correct

[Welshgasman]

Just walk your code line by line, that is what I do, even if going into a function like your GetObject.
Probably worth showing that code as well, as from within Access you have to qualify the Excel object at least to start with, to get links to the workbook.
I would also leave the app visible for testing. That way you can check the values in the code.
I would probably just copy the range from source to the next row in the target workbook/sheet.

THis is some code from one of my workbooks, where I copied the monthly data into a Master workbook.

Set wkbGCD = ActiveWorkbook
    Set sht = Sheets(strSheet)
    intLastGCD = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
    Debug.Print "Last GCD:" & intLastGCD
    intCopyRows = intLastGCD - 4 + 1
    Debug.Print "CopyRows " & intCopyRows


    'Now copy data to master workbook
    'Range("A4:J" & intLastGCD).Copy
    'wkbMaster.Sheets(strSheet).Activate
    'Range("A" & intMasterGCD + 1).PasteSpecial
    'Now try in one statement
    Workbooks(strMonthGCD).Worksheets(strSheet).Range("A4:L" & intLastGCD).Copy _
            Workbooks(strMasterGCD).Worksheets(strSheet).Range("A" & intMasterGCD + 1)

[DMT Dave]

Well after changing the declarations and cleaning the terminations, still can't seem to get the data over the the target page on the target file!!

after adding break points, i can't find my error!!!

updated

varPage = InputBox("Enter Page A-Z ?" & vbCrLf & vbCrLf & _        "Leave Blank To Abort", "ENTER PAGE")


Select Case varPage
Case Is = ""
    Exit Sub
Case Else


    strPath = "C:\Users\davem\Desktop\"
    strTargetFile = "Target File.xlsx"
    strSourceFile = "Source File.xlsx"
    
    'Changed From Dim xlAP As Excel.Application
            '   TO: Dim xlAP As Object
    Set xlAP = CreateObject("Excel.Application")
  
    Set xlSourceWB = xlAP.Workbooks.Open(strPath & strSourceFile) 'REMOVED GET OBJECT TO OPEN FROM xlAP
    Set xlTargetWB = xlAP.Workbooks.Open(strPath & strTargetFile) 'REMOVED GET OBJECT TO OPEN FROM xlAP
    
    Set xlSourceSHT = xlSourceWB.Worksheets(varPage)
    Set xlTargetSHT = xlTargetWB.Worksheets("Customers")
        
    xlAP.Visible = False
    
    With xlTargetSHT
    
    iSourceLR = xlSourceSHT.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row ' Find Last Row With Data
    iTargetLR = xlTargetSHT.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row ' Find Last Row With Data
    
    iTargetNR = iTargetLR + 1 ' Next Row To Append Data
    
        Debug.Print xlSourceSHT.Name & " - " & iSourceLR & " Total Rows" & vbCrLf & vbCrLf & _
                        xlTargetSHT.Name & " - " & "To Go From Row " & iTargetNR
            
        For i = 1 To iSourceLR
                    
            xlTargetSHT.Cells(iTargetNR, 1) = xlSourceSHT.Cells(2 + iSourceLR, 1)
        
        Next i
        
        iSourceLR = iSourceLR + 1
        
    End With
    
    xlTargetWB.Save
    xlTargetWB.Close
    
    xlAP.Quit
    
    Set xlAP = Nothing
    Set xlSourceWB = Nothing
    Set xlTargetWB = Nothing
    Set xlTargetSHT = Nothing

[Welshgasman]

You can't just add breakpoints, they are just stopping places.
I would expect your objects to be set, else it would complain.

What are the With and End With for, as you do not appear to use them?
As it is just two workbooks, you could upload them.

[June7]

See if this gets what you want:
(iTargetNR variable not really needed.)

    For i = 1 To iSourceLR
        xlTargetSHT.Cells(iTargetLR + i, 1) = xlSourceSHT.Cells(i + 1, 1)
    Next i

Or eliminate looping:

    xlTargetSHT.Range("A" & iTargetLR + 1 & ":" & "A" & iTargetLR + iSourceLR - 1).Value = _
                                      xlSourceSHT.Range("A2:" & "A" & iSourceLR).Value

Don't really need Select Case:
If varPage <> "" Then
... all code here
End If
However, using an InputBox for user input is tricky. What if they type anything but A-Z? So maybe you do need Select Case and expand it. Or modify code for InputBox to do some validation. Here is one simple example from my db:

                    While Not (Val(strInput) > 0 And Val(strInput) < 29)
                        strInput = InputBox("Enter from 1 to 28 number of days for sample curing period.", "Curing Period", 28)
                    Wend

And agree, the With xlTargetSHT isn't used so remove. If you want to take advantage of it, don't repeat xlTargetSHT within the block. References would be .Name and .Cells

[DMT Dave]

Thank you June7, i will read through what you have done here and compare before i try anything, but thank you...

Just noticed Val() in what you use, i will check it out on Google but assuming this is to eliminate Select Case and USe Val() in relevant ststement

Guessing Val() is either Value or Validate Function, again will check it out for future options to use

I think i just get used to using select case then making a note on the input (LEAVE BLANK TO ABORT) and exit sub to eliminate anything other than A-Z but yes, what if i typed June7 or WGM in the input, i guess then would Len() function help that and Len is always 1 ?

will check out though a little later


will check out your method

Kindest

[DMT Dave]

Hi June7, that has acheved the goal here

strPath = "C:\Users\davem\Desktop\"    strTargetFile = "Target File.xlsx"
    strSourceFile = "Source File.xlsx"
    
    Set xlAP = CreateObject("Excel.Application")
  
    Set xlSourceWB = xlAP.Workbooks.Open(strPath & strSourceFile)
    Set xlTargetWB = xlAP.Workbooks.Open(strPath & strTargetFile)
    
    Set xlSourceSHT = xlSourceWB.Worksheets(varPage)
    Set xlTargetSHT = xlTargetWB.Worksheets("Customers")
        
    xlAP.Visible = False
    
    iSourceLR = xlSourceSHT.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    iTargetLR = xlTargetSHT.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    xlTargetSHT.Range("A" & iTargetLR + 1 & ":" & "A" & iTargetLR + iSourceLR - 1).Value = _
                                      xlSourceSHT.Range("A2:" & "A" & iSourceLR).Value


    xlTargetWB.Save
    xlTargetWB.Close
    
    xlAP.Quit
    
    Set xlAP = Nothing
    Set xlSourceWB = Nothing
    Set xlTargetWB = Nothing
    Set xlTargetSHT = Nothing

I am going to look at this simple option from start to finish to see how this works and keep for future use

Thanks a lot

[June7]

Val() converts string number to real number (Val("123.41abc") returns 123.41). I used it because InputBox returns a string, not actual number. It wouldn't be needed in your case. You would need to verify that input is only a letter. Len() wouldn't assure that because numbers and symbols also have a length. Consider:

If InputBox("Enter Page A-Z ?" & vbCrLf & vbCrLf & "Leave Blank To Abort", "ENTER PAGE") LIKE "[A-Z]" Then

VBA is not case sensitive by default so lower case input will also match.

Could use Else to give users a message for incorrect input: MsgBox "Input must be a single letter."

[Micron]

If you need only upper or only lower case from an input box that returns only one letter maybe check that the asc value lies between 65 and 90 (A-Z) or 97 and 122 (a-z) as well as ensuring the input length = 1?

[DMT Dave]

Ah ok micron, i will look at asc values thank you indded and happy new year to all on here

thank you

[June7]

Did you see post 11? Don't need ASCII values.

[DMT Dave]

@ June7, your suggestion worked well

Thank you and happy new year to all