Solved Convert An Excel Macro To Run In Access VBA (1 Viewer)

[jo15765]

I am altering a spreadsheet from access vba. I have this Excel macro that makes a portion of the required changes, but this is excel vba not the access vba.

Can someone provide me with what this code would look like if it were access vba to control excel?

 Columns("H:I").Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "TestData"
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$B$1728").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
    Columns("A:B").Select
    ActiveWorkbook.Worksheets("TestData").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("TestData").Sort.SortFields.Add2 Key:=Range("A2:A1728"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("TestData").Sort
        .SetRange Range("A1:B1728")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
    Columns("A:A").EntireColumn.AutoFit

 

[Ranman256]

you can do this with 1 query. No code needed.
the query can remove duplicates.

I am altering a spreadsheet from access vba. I have this Excel macro that makes a portion of the required changes, but this is excel vba not the access vba. Can someone provide me with what this code would look like if it were access vba to control excel?

 Columns("H:I").Select Selection.Copy Sheets.Add After:=ActiveSheet Sheets("Sheet1").Select Sheets("Sheet1").Name = "TestData" ActiveSheet.Paste Application.CutCopyMode = False ActiveSheet.Range("$A$1:$B$1728").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes Columns("A:B").Select ActiveWorkbook.Worksheets("TestData").Sort.SortFields.Clear ActiveWorkbook.Worksheets("TestData").Sort.SortFields.Add2 Key:=Range("A2:A1728"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("TestData").Sort .SetRange Range("A1:B1728") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("A1").Select Columns("A:A").EntireColumn.AutoFit

 

[jo15765]

you can do this with 1 query. No code needed.
the query can remove duplicates.

With 1 query...meaning an access query?

I'm needing to keep it all done in Excel - but let access vba do the leg work

 

[Micron]

I'm needing to keep it all done in Excel - but let access vba do the leg work

That sounds contradictory but I think I understand. In that case, what you need to do is learn Automation. Maybe this will give you a starting point:

EXCEL Import And Export

or research automating Excel from Access

 

[Isaac]

something like this should get you started. you should never use Activate, Active, Select, in Excel vba. I've tried to correct it as best as I can, I recommend you go with this route even if you have to make some small tweaks to my code. I've created it based on what I think seemed to be the intentions of the Select's and Activate's.

dim excelapp as object, wb as object, ws as object, ws2 as object
set excelapp=createobject("excel.application")
set wb = excelapp.workbooks.open("path to file") 'EDIT ************'
set ws = wb.sheets("name") 'EDIT **********'
ws.Columns("H:I").Copy
    set ws2 = wb.Sheets.Add After:=wb.worksheets("name of first sheet")
    ws2.Name = "TestData"
    ws2.paste
    Application.CutCopyMode = False
    ws2.Range("$A$1:$B$1728").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
    ws2.Sort.SortFields.Clear
    ws2.Sort.SortFields.Add2 Key:=Range("A2:A1728"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws2.Sort
        .SetRange Range("A1:B1728")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    'Range("A1").Select '** see below line instead
    application.goto ws2.range("A1"),true
    ws2.Columns("A:A").EntireColumn.AutoFit

 

[jo15765]

something like this should get you started.

I am getting an error of:
Compile error:
Expected: end of statement

On this line

Set ws2 = wb.Sheets.Add After:=wb.worksheets("Data")

and it highlights this

After

 

[Isaac]

Sorry, try

Set ws2 = wb.Sheets.Add(After:=wb.worksheets("Data"))

 

[jo15765]

When I try to run the function, I get this error

Compile error:
Method or data member not found

On this

.CutCopyMode

full line is

Application.CutCopyMode = False

 

[Isaac]

sorry, change Application to my excelapp object.
excelapp.cutcopymode=false

 

[Isaac]

This is what I get for doing everything from memory

 

[jo15765]

This is what I get for doing everything from memory

FROM MEMORY!!!??? that is amazing! I hope I am one day this good

Now I am getting the error
Compile error:
Method or data member not found

on this

.Goto

and this is full line

Application.Goto ws2.Range("A1"), True

 

[Isaac]

darn it, it's the using Application. rather than Excelapp. again

Basically as you can see I have declared an Excel application instance and that variable needs to be used as the handle replacing Application. That handle can now do anything that Application can do in Excel vba, just like wb and ws can do anything that ThisWorkbook and a Worksheet variable could do in Excel vba.

 

[jo15765]

oh wait. same thing.
should be

excelapp