Import Excel Data into Access Form Issue 1

[bud31047]

Hi all,

I can use some help with some code. I have an Access form that is displaying some data from an Excel Spreadsheet. My code, meaning the data I want to display is being displayed, but I need help to make my code better.

The spreadsheet has links to external data. How can I turn off the Excel message box asking "-To update all linked information, click Yes."? How can I turn off the message box asking me if i wish to open the spreadsheet "Read Only", if someone else has it open already?

Also, if you could, take a look at my code and tell me what I have left out? Do I need to close the spreadsheet in my code? etc;

This is one some of my first code, so I am sure I have made a ton of mistakes. Tell how bad it is, I can take it.

Thanks for the help,
James

Private Sub btnPopForm_Click()
Dim shift As String
Dim ExcelData As Integer
Dim xlapp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet1 As Excel.Worksheet
Dim xlsheet2 As Excel.Worksheet
Dim xlsheet3 As Excel.Worksheet

Set xlapp = New Excel.Application

Set xlbook = xlapp.Workbooks.Open("r:\Palletizing Preshift Agenda.xls")
Set xlsheet1 = xlbook.Worksheets("1st Shift")
Set xlsheet2 = xlbook.Worksheets("2nd Shift")
Set xlsheet3 = xlbook.Worksheets("3rd Shift")

shift = Me.cmbShift.Value

If shift = "1st" Then
ExcelData = xlsheet1.range("E5")
ElseIf shift = "2nd" Then
ExcelData = xlsheet2.range("E5")
ElseIf shift = "3rd" Then
ExcelData = xlsheet3.range("E5")
ElseIf shift = "2nd" Then
ExcelData = 0
End If

Me.txtLinkData.Value = ExcelData

End Sub

 

[bud31047]

Okay. I know I need to close Excel. I just looked and I had about 10 instances of Excel running in memory. How I close this in my code?

James

 

[Remou]

Here you are:

Private Sub btnPopForm_Click()
Dim strShift As String
Dim ExcelData As Variant 'Best not use integer, check later
Dim xlapp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet

Set xlapp = New Excel.Application

Set xlbook = xlapp.Workbooks.Open("r:\Palletizing Preshift Agenda.xls")
strShift = Trim(Me.cmbShift.Value & "")
If strShift <> "" Then
'Less trouble to just use the value of the combo
'to get the sheet
    Set xlsheet = xlbook.Worksheets(strShift & " Shift")
    ExcelData = xlsheet.range("E5")
Else
'I think this is what you meant
    ExcelData = 0
End If

Me.txtLinkData.Value ExcelData

'Clean up.
Set xlsheet = Nothing
Set xlbook = Nothing
xlapp.Quit
Set xlapp = Nothing

End Sub

 

[bud31047]

Thanks for the tips Remou.
Your code works as advertised.

How can I turn off the prompt to "update the linked data" in the spreadsheet?
When I .quit the spreadsheet, how can I set the "save" property to FALSE, so it doesn't prompt me?

Would it be better to open the spreadsheet as "Read-Only" from the start?

Thanks,
James

 

[bud31047]

I solved the prompt to update;
Set xlbook = xlapp.Workbooks.Open("r:\Palletizing Preshift Agenda.xls", 2)

I am still struggling with the prompt to save the spreadsheet.

 

[bud31047]

I really should try harder before I post for help, because I solved my problem after 5 minutes of looking.

xlapp.DisplayAlerts = False

It works great.

James

 

[Remou]

Enjoy a star for making the extra effort to solve your problem.

 

[bud31047]

Thanks Remou