Access VBA for Excel Copy & Paste (1 Viewer)

[julia55]

I am brand new to the VBA world, and am using a macro I found on another website. I have been able to modify it so far to run, but it is erroring when it gets to the red text in the code. Any idea what is wrong with the code below? I basically want it to copy cells A2 through the end of the sheet on wbExported (this should include blank cells so I can use this to delete rows on wbAllData), and paste it starting in cell A2 on wbAllData on Sheet4. wbAllData is an existing workbook, so it does have to specifically paste into Sheet4.

Set wbExported = MyExcel.Workbooks.Open(strPathFile)
Set wbAllData = MyExcel.Workbooks.Open("Q:/PX_Overview.xlsm")
 
[COLOR=red]With wbExported.Sheet1.UsedRange[/COLOR]
[COLOR=red]Set rngUsed = .Offset(1, 0)[/COLOR]
[COLOR=red]End With[/COLOR]
[COLOR=red]With wbAllData.Sheet4[/COLOR]
[COLOR=red]rngUsed.Copy _[/COLOR]
[COLOR=red]    Destination:=.Cells(2, 1)[/COLOR]
[COLOR=red]End With[/COLOR]

 

[Brianwarnock]

Never run excel from access so cannot comment on that part but in excel the code would be

Dim rngused as range
Set rngused = worksheets.("sheet1").usedrange
Rngused.copy destination:=Worksheets.("sheet4").range("A2")

I suspect that you also need to include the keyword worksheets

Brian

 

[Rx_]

http://www.btabdevelopment.com/ts/default.aspx?PageId=47
For the Recordset (rst in the code) you will want to review how to create a recordset. Note that my rst is "rsReclMonDt" Mine might look something like this:

310   strSQLPAT = "SELECT [PAT123U-A].[Area], [PAT123U-A].Well_Name AS [Well Name], [PAT123U-A].ST, [PAT123U-A].[Regulatory Office], [PAT123U-A].[Regulatory Type], " & _
      "[PAT123U-A].[Permit Type], [PAT123U-A].[Date Submitted], [PAT123U-A].[Date Approved], [PAT123U-A].Approval_Time, [PAT123U-A].Status, [PAT123U-A].Form, [PAT123U-A].ID_Area "
320   strSQLPAT = strSQLPAT & "FROM [PAT123U-A] "
330   strSQLPAT = strSQLPAT & "WHERE ((([PAT123U-A].ID_Area) " & ID_Area & "))"
340   strSQLPAT = strSQLPAT & "ORDER BY [PAT123U-A].Area, [PAT123U-A].Well_Name, [PAT123U-A].[Regulatory Type] DESC; "          
          Debug.Print strSQLPAT ' prints SQL to Immediate window - can copy and paste it into a new Query as SQL          
360         Set rsReclMonDt = CurrentDb.OpenRecordset(strSQLPAT, dbOpenSnapshot, dbReadOnly) ' suggestion was dbOpenSnapshot in Access 2010 was not any faster than any other kind of recordset - it use to be much faster in Access 95

Note: the & ID_Area & is passed in by the user as an IN(3,7,5) as they make multiple selections in a list box.

And, this week there is a bonus for asking such a question. Note that Bob's site used APXL where I use ObjXL. Once you get it in Excel, you might want to save paper by adjusting the margins and orientation. Here is some code that works on Xerox and HP printers. Note too, in my dozens of Excel reports, I always start my data at row 5 so that the header information can fit at the top.

2700  On Error Resume Next
2710      With ObjXL.ActiveSheet.PageSetup
2720            .LeftMargin = ObjXL.Application.InchesToPoints(0.25)
2730            .RightMargin = ObjXL.Application.InchesToPoints(0.25)
2740            .TopMargin = ObjXL.Application.InchesToPoints(0.75)
2750            .BottomMargin = ObjXL.Application.InchesToPoints(0.75)
2760            .HeaderMargin = ObjXL.Application.InchesToPoints(0.3)
2770            .FooterMargin = ObjXL.Application.InchesToPoints(0.3)
              
2780          .PrintHeadings = False
2790          .PrintGridlines = False
2800          .PrintComments = xlPrintNoComments
2810          .PrintQuality = 600
2820          .CenterHorizontally = False
2830          .CenterVertically = False
2840          .Orientation = xlPortrait 'xlLandscape
2850          .Draft = False
2860          .PaperSize = xlPaper11x17
2870          .FirstPageNumber = xlAutomatic
2880          .Order = xlOverThenDown
2890          .BlackAndWhite = False
              '.Zoom = 56
2900            .Zoom = False
2910            .FitToPagesWide = 1
2920            .FitToPagesTall = False
              '.PrintErrors = xlPrintErrorsDisplayed
2930          .OddAndEvenPagesHeaderFooter = False
2940          .DifferentFirstPageHeaderFooter = False
2950          .ScaleWithDocHeaderFooter = True
2960          .AlignMarginsHeaderFooter = True
2970          .PrintTitleRows = "$1:$" & (intRowPos - 1)        ' repeats header row 1 to 5
2980          .LeftFooter = "Page &P of &N"
2990          .RightFooter = "&D"
3000        End With

Maybe this will get you started in the right direction.

Just one more thing... In a code window - References - you did check Microsoft Excel? The Excel object must be referenced or it won't work.

 

[julia55]

Thanks RX & Brianwarnock!

RX, I did have Excel checked in references. I was able to piece together what I needed with your references, and everything is working perfectly now!

 

[Rx_]

Thank youfor the feedback and note of success. If you want to post your working version, it would probably help someone else searching for a solution.