Replacing Specific XL Page

[Micron]

My code was for a single or few sheet replacements. If you wanted to do all, you'd use your original?
I imagine it could be adapted to deal with a single range (e.g. D to H) by creating a loop counter from Asc(Ucase("a")) to Asc(Ucase("h") instead. Should not need the array in that case.

[DMT Dave]

Hi Micron, would my attempt in post 12 do it, not tried yet but hoping that the plan behind the code makes sense to you ?

[Micron]

Maybe - if you correct this line
If MsgBox("Contacts Starting With Letter: ... "UPDATE NOW ?")= vbNo Then

Msgbox "your message here" (with or without parentheses) is used when the function should not return anything.
If returning the result of a user choice, then it must be returned to a variable. So more like where you have declared Result As Integer:

Result = Msgbox (...)
If Result = something Then

and the function parameters must be enclosed in parentheses as shown when it returns a value.

As for whether or not your Automation code will succeed is impossible to say, but I don't see any issue with it. Seems more complicated than transferspreadsheet, unless you prefer to update the sheet rather than replace it.

[DMT Dave]

Thanks a lot, i have your code to play with as well as WGM suggestions

the sheet is an update rather than replacement, also see your point about msg box, will adapt to that

kindest

[DMT Dave]

Hi Guy's I am struggling with this a little!!!!

Here is my code to clear a contacts page starting with a specific initial, when i open the Xl file to check a page has been updated, there are no pages whatsoever, thankfully as i have been playing around with it, i keep making a back up of the Xl File

What would you say is wrong here ?
Recordset update
Clearing Contents of Page
Im I setting the XL sheet to update correctly ?

The code is running through to the MsgBox to tell me updated
???

If Me.cboContactOptions = "Update A-Z Range PO Sheet" ThenDim strInput As String
strInput = InputBox("Enter The Single Letter A-Z Page To Update Contacts ?" & vbNewLine & vbNewLine & _
"ie: To Update Page With Contacts Starting With: S Just Enter S etc....", "ENTER LETTER")
    Set xl = CreateObject("Excel.Application")
    xl.Visible = True
    Set xlWrkBk = GetObject(srcPath & srcFile)
    Set xlsht = xlWrkBk.Worksheets(strInput)
        xlsht.Cells.ClearContents


        Set rs = CurrentDb.OpenRecordset("Select tblDealers.Name, tblDealers.Town, tblDealers.Postcode From tblDealers WHERE left(ucase([Name]),1)='" & strInput & "'")
        xlsht.Cells(1, 1).CopyFromRecordset rs
        MsgBox ("Contacts Page: " & strInput & " " & "Now Updated."), vbInformation + vbOKOnly, "UPDATED"
        xl.Quit
        Set xl = Nothing
        Set xlWrkBk = Nothing
        Set xlsht = Nothing


End If

[Welshgasman]

You not appear to open the workbook?
What does GetObject do?, the code look like?

Again, have you tried walking through the code with F8 ?

I'm pretty sure you cannot just have Set xlsht = xlWrkBk.Worksheets(A), you need to have "A"

[DMT Dave]

Hi WGM, would this little adaption do it ?

Set xlWrkBk = xl.Workbooks.Open(srcPath & srcFile)
Set xlsht = xlWrkBk.Worksheets(" & strInput & ")

Putting quotes in the input

Opening XL

[Welshgasman]

TBH re opening, it looks like it should. That is why I test with F8, to make sure those variables are correct? Often people forget the trailing \ for srcPath ?
As for the worksheet reference, no, you cannot just chuck " in some code and hope for the best.

Again!, walking through the code and hovering over the variables will show you what you have?
For this situation I would probably use the CHR() function for the " character.

[Micron]

MsgBox ("Contacts Page: " & strInput & " " & "Now Updated."), vbInformation + vbOKOnly, "UPDATED"
xl.Quit

Where are you saving the workbook?

[DMT Dave]

Ahhh Micron, i need to use xl.save before xl.quit ????

Durrr thats why my XlFile is Blank ?

[Micron]

I would have thought that even when saving in vba you'd get a prompt (unless you turn off application screen updating and/or messaging), but yes. If you don't replicate with vba what you'd do when interacting with the workbook manually, you can't expect the same results from your code.

[DMT Dave]

Done thank you guys

        Set rs = CurrentDb.OpenRecordset("Select tblDealers.Name, tblDealers.Town, tblDealers.Postcode From tblDealers WHERE left(ucase([Name]),1)='" & strInput & "'")        xlsht.Cells(1, 1).CopyFromRecordset rs
        MsgBox ("Contacts Page: " & strInput & " " & "Now Updated."), vbInformation + vbOKOnly, "UPDATED"
        xlWrkBk.SaveAs srcPath & srcFile
        xlWrkBk.Close
        xl.Quit
        Set xl = Nothing
        Set xlWrkBk = Nothing
        Set xlsht = Nothing

[Welshgasman]

@DMT Dave
I owe you an apology for post #21

You can refer to a sheet with a variable as you did.
I have even done it myself, but forgot.

' Now copy Passengers
strSheet = "Passengers"
wkbGCD.Sheets(strSheet).Activate
Set sht = ActiveSheet
intLastGCD = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
Range("A2:J" & intLastGCD).Copy
wkbMaster.Sheets(strSheet).Activate
Range("A2").PasteSpecial


' Now copy Destinations
strSheet = "Destinations"
wkbGCD.Sheets(strSheet).Activate
Set sht = ActiveSheet

[DMT Dave]

WGM thank you and no you don't like all you out there, you are most helpful

Thank you