Cant create excel "List" using eccess VBA

[trevorC via AccessMonster.com]

Hi All,
I'm trying to create a list in excel "To stop people from un-sorting the
data". I'm trying to do this using VBA, so far i have created my spreadsheet
and added titles, sub totals and the data from several access queries, then
formated the sheet with borders, colors and cell alignment and sizes. I can
then select the area that i want for my List including headers, but the code
won't create a list.
the code we copied from an excel macro after creating a list. I have tried
several way of referencing the selection but it won't work. My other code was
also made like this and it all works ok as listed.

Code sample:
Dim Excel_Application As Excel.Application
Dim Excel_Workbook As Excel.Workbook
Dim Current_Worksheet As Excel.Worksheet
Set Excel_Workbook = GetObject(gg)
Set Excel_Application = Excel_Workbook.Parent
Excel_Workbook.Worksheets(1).Name = "Transfer Details"
Set Current_Worksheet = Excel_Workbook.Worksheets("Transfer Details")
Excel_Application.WindowState = xlMinimized
Excel_Application.Visible = True
Excel_Workbook.Windows(1).Visible = True
Current_Worksheet.Rows("1:1").Insert Shift:=xlDown
With selection
Current_Worksheet.Range("C55").MergeCells = True
End With

DoCmd.GoToRecord , , acNext
Next i
Current_Worksheet.Rows("5:5").Select
With selection
Current_Worksheet.Rows("5:5").Delete Shift:=xlUp
End With
Current_Worksheet.Range("B3:E4").Select
With selection
Current_Worksheet.Range("B3:E4").HorizontalAlignment = xlCenter
Current_Worksheet.Range("B3:E3").MergeCells = True
Current_Worksheet.Range("B3:E4").Font.Bold = True
End With

rng1 = "$A$11:$O$" & (RC + 11)

Current_Worksheet.Range(rng1).Select
With selection
Current_Worksheet.Range(rng1).ListObjects.Add(xlSrcRange, Range(rng1), ,
xlYes).Name = "List1"
End With
Excel_Workbook.Save
Excel_Application.Quit

I cant get this line of code to work
.ListObjects.Add(xlSrcRange, Range(rng1), , xlYes).Name = "List1"
all of the other code works fine

All help is appreciated.
regards
TrevorC

 

[Alex Dybenko]

Hi
first of all - not clear what is RC in this line:
rng1 = "$A$11:$O$" & (RC + 11)

Then you can do the following. Open your sheet, start macro recorder, make
list the way you want, stop recorder and check what code was generated. Now
make your code similar to generated one

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[trevorC via AccessMonster.com]

Hi
first of all - not clear what is RC in this line:
rng1 = "$A$11:$O$" & (RC + 11)

Hi,
the RC just adds a number for the rows added, anyway i fixed this one with
the line below

Current_Worksheet.ListObjects.Add(xlSrcRange, , xlYes).Name = "List1"

Now i'm having trouble trying to get a Note inserted using the same method.

Any Help appriciated.
Regards
TrevorC