Hi,
User working with window 7, has data in Excel 2010. When she wants to refresh the data, which is linked to an external resource database in MS Access 2010, she gets the error which is mentionned in the title. We recently upgraded from 2007 to 2010 Ive updated the code to reflect *.accdb in stead of *.mbx but I'm not able to change this link. Each time when I refresh and browse to the new drive letter and select the new location of the database, it returns with the same error message.
Excel Version is 2010.
Access Version is 2010.
Can somebody advice?
Thx
Geert
Sheets("Opened Data").Select
Range("A2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Closed Data").Select
Range("A2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Closed Data").Select
Cells.Select
Selection.Copy
Sheets("Closed").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Opened Data").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Opened").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.DisplayAlerts = False
Sheets("Opened Data").Delete
Sheets("Closed Data").Delete
Application.DisplayAlerts = True
Range("A1").Select
Sheets("Closed").Select
Range("A1").Select
Dim sFileName As String, sPath As String
sPath = "L:\Retail Store Development\Store Launch Team\Reporting\Yellow Page Reporting\"
sFileName = "Yellow Page Report " & Format(Now(), "mmddyy")
ActiveWorkbook.SaveAs (sPath & sFileName)
Sheets("Opened").Select
Columns("A:A").ColumnWidth = 16.14
Columns("B:B").ColumnWidth = 8.14
Columns("C:C").ColumnWidth = 32.57
Columns("D:D").ColumnWidth = 16.43
Columns("E:E").ColumnWidth = 20.29
Columns("F:F").ColumnWidth = 31.57
Columns("G:G").ColumnWidth = 17.29
Columns("H:H").ColumnWidth = 5.14
Columns("I:I").ColumnWidth = 8.29
Columns("J:J").ColumnWidth = 13.29
Sheets("Closed").Select
Columns("A:A").ColumnWidth = 16.14
Columns("B:B").ColumnWidth = 8.14
Columns("C:C").ColumnWidth = 32.57
Columns("D:D").ColumnWidth = 16.43
Columns("E:E").ColumnWidth = 20.29
Columns("F:F").ColumnWidth = 31.57
Columns("G:G").ColumnWidth = 17.29
Columns("H:H").ColumnWidth = 5.14
Columns("I:I").ColumnWidth = 8.29
Columns("J:J").ColumnWidth = 13.29
Sheets("Opened").Select
Cells.Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Rows("1:1").Select
Selection.Font.Bold = True
Sheets("Closed").Select
Cells.Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Rows("1:1").Select
Selection.Font.Bold = True
Range("A1:J1").Select
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
Range("A2").Select
Sheets("Opened").Select
Range("A1:J1").Select
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
Range("A2").Select
ActiveWorkbook.Save
' Get a reference to the Access Application object.
Set appAccess = CreateObject("Access.Application")
' Open your db.
' Modify the path as needed.
appAccess.OpenCurrentDatabase "L:\Retail Store Development\Store Launch Team\Tracker Majigger\Tracker-Majigger.accdb"
' hide the application.
appAccess.Visible = False
appAccess.DoCmd.RunMacro "mcrYellowPagesUpdates"
' Close the database.
appAccess.CloseCurrentDatabase
' Quit Access.
appAccess.Quit
' Close the object variable.
Set appAccess = Nothing
MsgBox "Process complete. Click OK to exit macro."
End Sub
User working with window 7, has data in Excel 2010. When she wants to refresh the data, which is linked to an external resource database in MS Access 2010, she gets the error which is mentionned in the title. We recently upgraded from 2007 to 2010 Ive updated the code to reflect *.accdb in stead of *.mbx but I'm not able to change this link. Each time when I refresh and browse to the new drive letter and select the new location of the database, it returns with the same error message.
Excel Version is 2010.
Access Version is 2010.
Can somebody advice?
Thx
Geert
Sheets("Opened Data").Select
Range("A2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Closed Data").Select
Range("A2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Closed Data").Select
Cells.Select
Selection.Copy
Sheets("Closed").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Opened Data").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Opened").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.DisplayAlerts = False
Sheets("Opened Data").Delete
Sheets("Closed Data").Delete
Application.DisplayAlerts = True
Range("A1").Select
Sheets("Closed").Select
Range("A1").Select
Dim sFileName As String, sPath As String
sPath = "L:\Retail Store Development\Store Launch Team\Reporting\Yellow Page Reporting\"
sFileName = "Yellow Page Report " & Format(Now(), "mmddyy")
ActiveWorkbook.SaveAs (sPath & sFileName)
Sheets("Opened").Select
Columns("A:A").ColumnWidth = 16.14
Columns("B:B").ColumnWidth = 8.14
Columns("C:C").ColumnWidth = 32.57
Columns("D:D").ColumnWidth = 16.43
Columns("E:E").ColumnWidth = 20.29
Columns("F:F").ColumnWidth = 31.57
Columns("G:G").ColumnWidth = 17.29
Columns("H:H").ColumnWidth = 5.14
Columns("I:I").ColumnWidth = 8.29
Columns("J:J").ColumnWidth = 13.29
Sheets("Closed").Select
Columns("A:A").ColumnWidth = 16.14
Columns("B:B").ColumnWidth = 8.14
Columns("C:C").ColumnWidth = 32.57
Columns("D:D").ColumnWidth = 16.43
Columns("E:E").ColumnWidth = 20.29
Columns("F:F").ColumnWidth = 31.57
Columns("G:G").ColumnWidth = 17.29
Columns("H:H").ColumnWidth = 5.14
Columns("I:I").ColumnWidth = 8.29
Columns("J:J").ColumnWidth = 13.29
Sheets("Opened").Select
Cells.Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Rows("1:1").Select
Selection.Font.Bold = True
Sheets("Closed").Select
Cells.Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Rows("1:1").Select
Selection.Font.Bold = True
Range("A1:J1").Select
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
Range("A2").Select
Sheets("Opened").Select
Range("A1:J1").Select
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
Range("A2").Select
ActiveWorkbook.Save
' Get a reference to the Access Application object.
Set appAccess = CreateObject("Access.Application")
' Open your db.
' Modify the path as needed.
appAccess.OpenCurrentDatabase "L:\Retail Store Development\Store Launch Team\Tracker Majigger\Tracker-Majigger.accdb"
' hide the application.
appAccess.Visible = False
appAccess.DoCmd.RunMacro "mcrYellowPagesUpdates"
' Close the database.
appAccess.CloseCurrentDatabase
' Quit Access.
appAccess.Quit
' Close the object variable.
Set appAccess = Nothing
MsgBox "Process complete. Click OK to exit macro."
End Sub
Last edited: