opening worksheet from VB

A

Alex

I'm opening Excel workbook from MS Access. The program
checks whether the file is opened. When the file is not
opened, it's working good enough. But, when it's opened,
after a user click Ok for macros and read-only, it appears
for a moment and disappears.

Could anybody advise anything?

Thanks

Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim strFileName As String, _
logFileIsOpened As Boolean

strFileName = "Filename"
logFileIsOpend = False


Set xlApp = CreateObject("Excel.Application")
DoCmd.SetWarnings False
xlApp.DisplayAlerts = False
xlApp.AskToUpdateLinks = False

logFileIsOpened = FileLocked(strFileName)

If logFileIsOpened = True Then
Set xlBook = GetObject(strFileName)
Set xlSheet = xlBook.Worksheets(strRecipeNum)

xlSheet.Activate
DoCmd.SetWarnings True
xlBook.Application.DisplayAlerts = True

xlBook.Application.AskToUpdateLinks = True
xlBook.Application.Visible = True
xlSheet.Visible = xlSheetVisible

Else
Set xlBook = xlApp.Workbooks.Open(strFileName)
Set xlSheet = xlBook.Worksheets(strRecipeNum)
xlSheet.Activate
DoCmd.SetWarnings True
xlApp.DisplayAlerts = True
xlApp.AskToUpdateLinks = True
xlApp.Visible = True
xlSheet.Visible = xlSheetVisible
End If
 
A

Alex

Thanks,
It looks like I've resolved it by using MyXL as Object and
API functions from the help file for GetObject

Alex

Dim MyXL As Object ' Variable to hold reference
' to Microsoft Excel.
Dim xlSheet As Excel.Worksheet
MyXL.Application.Visible = True
MyXL.Parent.Windows(1).Visible = True
Set xlSheet = MyXL.Worksheets(strNumber)

xlSheet.Activate
DoCmd.SetWarnings True
MyXL.Application.DisplayAlerts = True

MyXL.Application.AskToUpdateLinks = True
MyXL.Application.Visible = True
xlSheet.Visible = xlSheetVisible
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top