Jump to content

VB code issues between Access/Excel

Recommended Posts


I have a vb macro that extracts comments and specific colors from a workbook that works in excel, but I want to put the vb in an access db and I'm running into issues. Here is the code I execute in excel on open:

Sub form()

Application.DisplayAlerts = False
    Dim oFN As Object
    Set oFN = Application.FileDialog(3)
    oFN.AllowMultiSelect = False
    If oFN.Show Then
        fn = oFN.SelectedItems(1)
        Exit Sub
    End If
'Open and copy BoW
    Dim cpwb As Workbook
    Dim bowOld As Worksheet
    Set cpwb = Application.Workbooks.Open(fn)
    Set bowOld = cpwb.Sheets("Sheet")
    Dim dest As Workbook
    Dim bow As Worksheet
    Set dest = Application.ThisWorkbook
    Set bow = dest.Sheets("SheetLinked")
    bowOld.Copy bow
    cpwb.Close (False)
'Format workbook
    ThisWorkbook.Sheets("Sheet").Name = "SheetLinked"
    Set bow = ThisWorkbook.Sheets("SheetLinked")
'Add Sheet 1
    Dim ws1 As Worksheet
    Set ws1 = ThisWorkbook.Sheets.Add(after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ws1.Name = "Comments"
    ws1.Range("A1").Value = "Location"
    ws1.Range("B1").Value = "Comment"
    Dim ws2 As Worksheet
'Add Sheet 2
    Set ws2 = ThisWorkbook.Sheets.Add(after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ws2.Name = "Color"
    ws2.Range("A1").Value = "Location"
    ws2.Range("B1").Value = "Comment"
'Get Comments
    Dim cmt As Comment
    Dim strComment As String
    Dim strLocation As String
    f = 2
    For Each cmt In bow.Comments
      ws1.Range("A" & f).Value = cmt.Parent.Address
      ws1.Range("B" & f).Value = cmt.Text
      f = f + 1
'Get Colors
    Dim MyRange As Range
    Dim LastRow As Long
    LastRow = bow.Cells(Cells.Rows.Count, "A").End(xlUp).Row
    Set MyRange = bow.Range("A10:AV" & LastRow)
    f = 2
    For Each c In MyRange
        If c.Interior.Color = 15849925 Or c.Interior.Color = 10213316 Or c.Interior.Color = 12040422 Then
            ws2.Range("A" & f).Value = c.Address
            If c.Interior.Color = 12040422 Then ws2.Range("B" & f).Value = "Purple"
            If c.Interior.Color = 15849925 Then ws2.Range("B" & f).Value = "Blue"
            If c.Interior.Color = 10213316 Then ws2.Range("B" & f).Value = "Green"
            f = f + 1
        End If


End Sub

So I tried to adapt it to work in access and then I ran into trouble:

Sub RebuildDB()
    Dim xlApp As Object
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    xlApp.AskToUpdateLinks = False

    Dim oFN As Object
    Set oFN = Application.FileDialog(3)
    oFN.AllowMultiSelect = False
    If oFN.Show Then
        fn = oFN.SelectedItems(1)
        Exit Sub
    End If

    MsgBox fn
    Dim cpwb As Excel.Application.Workbook
'    Dim bowOld As xlApp.Worksheet

Everything works fine up until I start to dim the workbook pages. Once I uncomment any of the lines referencing the workbook, it runs and nothing happens. If I Leave them commented out, the file selector pops up and displays the file name.


Any idea what I'm doing wrong?

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.