Jewtus

VB code issues between Access/Excel

1 post in this topic

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)
    Else
        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
    bow.Delete
    cpwb.Close (False)
    
'Format workbook
    ThisWorkbook.Sheets("Sheet").Name = "SheetLinked"
    Set bow = ThisWorkbook.Sheets("SheetLinked")
    ThisWorkbook.Sheets("Comments").Delete
    ThisWorkbook.Sheets("Color").Delete
    
'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
    Next
    
'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
    Next

    ThisWorkbook.Save
    Application.Quit

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)
    Else
        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