UtterAccess Discussion Forums > Modify Excel Vba To Run In Access Module?

classicxman

Apr 9 2012, 12:50 PM

I have the following codes which I run directly in Excel to delete links, delete sheets and save a new email version of the workbook. I would like to run this code from Access as part of my regular refresh batch, instead of Excel, so I can keep all my Excel files macro-free. How do I need to modify the code to do this? Muchos Gracias!

CODE

Sub DeleteLinks()
    Dim Ws As Worksheet, FirstSheet As Worksheet
    Set FirstSheet = ActiveSheet
    Application.ScreenUpdating = False
    For Each Ws In Worksheets
        Ws.Activate
     'Remove formulas & external links
        With Cells
            .Select
            .Copy
            Selection.PasteSpecial Paste:=xlValues
            Application.CutCopyMode = False
        End With
     'Remove Hyperlinks
        Cells.Hyperlinks.Delete

        [A1].Select
    Next
    FirstSheet.Activate
End Sub

CODE

Sub DeleteSheet()
  Application.DisplayAlerts = False
    Sheets("SheetName1").Delete
    Sheets("SheetName2").Delete
    Sheets("SheetName3").Delete
  Application.DisplayAlerts = True
End Sub

CODE

Sub MailVersion()
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim NewFileName As String

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

        FileExtStr = ".xlsx": FileFormatNum = 51
        TempFilePath = "C:\autoemailfiles\"
        TempFileName = Range("dashboard").Value
        NewFileName = TempFileName & " " & Format$(Date, "mm-dd-yyyy")

        With ActiveWorkbook
            .SaveAs TempFilePath & NewFileName, FileFormat:=FileFormatNum
            .Close SaveChanges:=False
        End With

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

thegeek

Apr 9 2012, 01:22 PM

You'll need to set a reference to the Excel Object library I believe. Then you can create an Excel object in Access VBA. Here is some "air code":

Dim xlsObj As Object
Dim xlsRange As Excel.Range
Dim xlsSheet As Excel.Sheet

Set xlsObj = CreateObject("Excel.Application")
xlsObj.Workbooks.Open "[path to your workbook]"
With xlsObj
Set xlsSheet = .Activesheet

etc...

Bob G

Apr 9 2012, 01:24 PM

here is an example of how to do your deletesheet.

CODE

Set oApp = CreateObject("Excel.Application")
    oApp.Visible = False
    oApp.Workbooks.Open FileName:=strfilename
    oApp.UserControl = True

oApp.Application.DisplayAlerts = False
oApp.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
oApp.ActiveWindow.SelectedSheets.Delete

classicxman

Apr 9 2012, 01:31 PM

Thank you. I love examples!

classicxman

Apr 9 2012, 03:23 PM

Any "example" for how to do the deletelinks() sub?

I was able to get some working code to copy/paste in the first worksheet. But I can't get it to loop through all the worksheets in the workbook.

CODE

Sub DeleteLinks()
    Dim Ws As Worksheet, FirstSheet As Worksheet
    Set FirstSheet = ActiveSheet
    Application.ScreenUpdating = False

    For Each Ws In Worksheets
        Ws.Activate

     'Remove formulas & external links
        With Cells
            .Select
            .Copy
            Selection.PasteSpecial Paste:=xlValues
            Application.CutCopyMode = False
        End With

     'Remove Hyperlinks
        Cells.Hyperlinks.Delete

        [A1].Select
    Next

    FirstSheet.Activate

End Sub

Bob G

Apr 10 2012, 06:29 AM

you may not need to loop thru all the worksheets. In your case, you are working on all the cells in all the worksheets. You should be able to select them all at once and perform what you need.

try this extra piece of code ****untested****

CODE

Set oApp = CreateObject("Excel.Application")
    oApp.Visible = False
    oApp.Workbooks.Open FileName:=strfilename
    oApp.UserControl = True

oApp.Application.DisplayAlerts = False
oApp.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
oApp.ActiveWindow.SelectedSheets.Delete
oApp.ActiveWorkbook.Sheets.Select
oApp.cells.hyperlinks.delete

classicxman

Apr 10 2012, 10:15 AM

Thank you Bob. I can see how that would work for something like a hyperlink or a connection where the code can identify, select and delete. How would this look if I wanted to go through each sheet and replace formulas with values?

Bob G

Apr 10 2012, 10:18 AM

off the top of my head and only for conversation purposes.

is what you are describing basically selecting the entire worksheet, the selecting copy, then paste special ?

classicxman

Apr 10 2012, 10:53 AM

Basically, yes.

Bob G

Apr 10 2012, 12:49 PM

then it would work the same basic way. Selecting all the worksheets and then all the cells and copy and paste formulas.

you can record a macro that does the steps and see what the excel vba would look like

JonSmith

Apr 10 2012, 12:55 PM

Can I ask what the overall process is (As in what/why you are exporting from Access. What is in the Excel file etc) as I think there are other ways of tackling this that would remove the need to delete sheets and links etc and could all be done Access side.

Bob G

Apr 10 2012, 12:57 PM

@jon,

The way I read this was that the OP wanted to move the "work" from Excel to Access.

Bob

JonSmith

Apr 10 2012, 01:04 PM

Hi Bob, I think I am reading it the other way, Access work to Excel. But the reason I am after a little bit more info is because I think this is the same thing that NineIron is asking about in another topic right now and I think some of the solutions proposed there (Pushing the data to a pre-defined spreadsheet avoiding links and MS-Query so as to allow emailing) might be better options then what is happening here. Need a little more detail to be sure though, I could be barking up the wrong tree.

Bob G

Apr 10 2012, 01:17 PM

will have to wait to see which way it really is.

classicxman

Apr 10 2012, 03:21 PM

Firstly, thanks for all your replies. I have something that is 98% there now, but let me try and better explain what I am doing.

1) I have a lot of databases and queries built in Access than run every day through a master batch file. These are pulling in fresh data from a variety of sources.
2) I have linked data tables (from Access tables) in Excel files to create Dashboards with graphs, statistics and other relevant information that is emailed to a distribution. These Excel files contain a lot of pivot tables, vlookups, array formulas, etc., to provide the "graphs, statistics and other relevant information" I need.
3) Rather than emailing out a 50mb Excel file, I wanted convert all formulas to values, delete the sheets with linked tables that I don't need anymore, and create a slimmed down email version of the Excel file.
4) I already had the Excel macros to do this, but I wanted to run it from my Access master batch file directly--hence, the crux of my biscuit (problem, as it were)

This was my original Excel code which worked great:

CODE

Sub DeleteLinks()
    Dim Ws As Worksheet, FirstSheet As Worksheet
    Set FirstSheet = ActiveSheet
    Application.ScreenUpdating = False
    For Each Ws In Worksheets
        Ws.Activate
     'Remove formulas & external links
        With Cells
            .Select
            .Copy
            Selection.PasteSpecial Paste:=xlValues
            Application.CutCopyMode = False
        End With

        [A1].Select
    Next
    FirstSheet.Activate
End Sub

This is where I am at with the Access version. The only problem now, is that it is not fully releasing the Excel file as I cannot open it until I quit Access. What am I still doing wrong?

CODE

Function DeleteLinks()

Dim xlApp As Excel.Application
Dim Ws As Worksheet, FirstSheet As Worksheet
Set xlApp = CreateObject("Excel.Application")
Set FirstSheet = ActiveSheet
With xlApp.Application
    .Visible = True
    .ScreenUpdating = False
    .Workbooks.Open "C:\Queries\Coating_Editing\test4.xlsx"
        For Each Ws In Worksheets
            Ws.Activate
    'Remove formulas & external links
            With Ws.Cells
                .Select
                .Copy
                .PasteSpecial Paste:=xlPasteValues
                xlApp.Application.CutCopyMode = False
            End With
            Ws.[A1].Select
        Next
    .Sheets("Summary").Activate
    'Save
    .ActiveWorkbook.Save
    .DisplayAlerts = False
    .ActiveWorkbook.Close SaveChanges:=True
    .Application.Quit

End With
Set xlApp = Nothing

End Function

Bob G

Apr 10 2012, 05:41 PM

this should be what you need.

xlApp.Close
set xlApp = Nothing

classicxman

Apr 10 2012, 06:01 PM

Thanks Bob!

classicxman

Apr 11 2012, 07:37 AM

QUOTE (JonSmith @ Apr 10 2012, 07:04 PM)

Hi Bob, I think I am reading it the other way, Access work to Excel. But the reason I am after a little bit more info is because I think this is the same thing that NineIron is asking about in another topic right now and I think some of the solutions proposed there (Pushing the data to a pre-defined spreadsheet avoiding links and MS-Query so as to allow emailing) might be better options then what is happening here. Need a little more detail to be sure though, I could be barking up the wrong tree.

While the code is working now, Jon's comment above got me thinking that there is probably a more efficient way to do what I am doing. Instead of creating a linked data table in Excel which pulls from my Access table and refreshing each time, have Access push the data into Excel eliminating the links. If I have an Excel file called "C:\Dashboard.xlsx" with a worksheet called "Data", what is the best way to transfer all records from my Access table "FinalDataTable" into my Excel worksheet "Data"? I would need the code to overwrite existing records in "Data" without any prompts. Can you point me in the right direction? Thanks.

Bob G

Apr 11 2012, 07:53 AM

i think if you create a new thread for this new scenario you will get more people looking to give a good answer. You can also search UA

JonSmith

Apr 11 2012, 02:59 PM

Hi Classicxman,

See attached my example of pushing data to an existing Excel file. I choose to use an Excel template file when doing this as it opens a new instance of the file and forces the user to save a new copy somewhere keeping the original template always intact.

You need to set a Reference to the 'Microsoft Office Excel Object Library' in the Access file otherwise it won't run. I used a dynamic named range in Excel as the source data for my Pivot Tables and Charts, this means that now matter how much data you add in it will always include all of that in them and I have written some quick code on each one so they refresh each time you select them.
Apart from that it should be self-explanatory from the files.

Good luck if you choose to go this way and don't hesitate to ask for any more help.

JonSmith

classicxman

Apr 11 2012, 03:03 PM

Jon--that is great. Thank you. I'll give it a try.

classicxman

Apr 11 2012, 03:36 PM

Jon--I noticed you have the range set to 65,536 rows. Will this same procedure work if I have 250,000+ records to transfer?

Set rng = wks.Range("A2:G65536")

JonSmith

Apr 11 2012, 03:51 PM

Hi Classicxman,

That was the row limit in Excel 2003, I have just tested changing that to row 5 on my example and seeing if it still runs. I still adds all the records (which go way past 5) so I don't think that number is particularly important from a last row perspective. As long as Excel has enough rows it will add them all (from what I can tell, test it and see how it goes).

JS

classicxman

Apr 14 2012, 08:10 AM

Jon or anyone else....

This code works great, except I have a few numeric flag fields in my table that contain ones and zeroes. When the table is copied to Excel, they are converted into dates (1/0/1900 and 1/1/1900). I've tried formating the corresponding columns in the Excel template file as numbers, but still doesn't work. Any ideas?

CODE

Function pte()
On Error GoTo MyError
Dim rst As DAO.Recordset
Dim customQuery As String
Dim cnt As Integer
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim rng As Excel.Range
Dim fileName As String
Dim fld As DAO.Field

    Set appExcel = Excel.Application
    '
    Set wbk = appExcel.Workbooks.Add(Application.CurrentProject.Path & "\testPTE.xltx")
    Set wks = wbk.Worksheets(1)
    Set rng = wks.Range("A2:R200001")

    Set rst = CurrentDb.OpenRecordset("EditMaster")
    If (rst.RecordCount > 0) Then
        cnt = 1
        For Each fld In rst.Fields
            wks.Cells(1, cnt).Value = fld.Name
            cnt = cnt + 1
        Next fld
        Call rng.CopyFromRecordset(rst, 200000, 18)
        wks.Columns.AutoFit
    End If

TidyUp:
    rst.Close
    Set rst = Nothing
    appExcel.Visible = True
    Set appExcel = Nothing

Exit Function
MyError:
    MsgBox "An error has occured", vbCritical
    'Enter any error loggin stuff here
    GoTo TidyUp
End Function

Bob G

Apr 14 2012, 08:20 AM

when i put zero or one in a worksheet and then format as date I get the dates you specify. I change the format and it reverts back to the number. What is the actual value that shows in your cell without formatting ?

classicxman

Apr 14 2012, 08:36 AM

The cell value shows as 1/0/1900

classicxman

Apr 14 2012, 08:40 AM

I tried putting this into the code but I'm not sure it's correct. It doesn't seem to help.

CODE

wks.Columns(Array("Leak_2", "RefWk_2", "P1_2", "P7_2", "P30_2", "P90_2", "P360_2")).NumberFormat = "0"

classicxman

Apr 14 2012, 09:00 AM

Got it!!! I didn't realize this would work, but I just went into Excel and recorded the macro for what I wanted to do, then pasted the code into my Access code and voila!

CODE

        Range("F:F,L:L,N:R").Select
        Range("Table1[[#Headers],[P1_2]]").Activate
        Selection.NumberFormat = "General"
        Application.Goto Reference:="R1C1"

JonSmith

Apr 14 2012, 12:25 PM

Glad to hear you are happy with the code and were able to work out your formatting problem. Just fyi you don't need the select lines, you can just set them directly, eg

CODE

Range("A1).NumberFormat = "General"