Access problem

[moonvalley]

When I call an excel file in access excel opens but by default hides all of it's sheets.

How do I tell access to make excel open with all normal sheets visible?

Please help, I've tried almost everything.

deborah

 

[Yogi Anand]

Hi Deborah:

Just wondering ... have you tried from within ACCESS, Importing or Linking a Table from an Excel workbook? -- that should expose all the worksheets in the workbook.

 

[moonvalley]

Tell me how you would suggest, everything else I have here works so far, I need to do this portion for the unhide, and the schedule the code.

Function IRCautodata()
On Error GoTo IRCautodata_Err

If WeekDay(Date, vbMonday) = 1 Then
gbldtStart = (Date - 4) + #8:30:00 PM#
gbldtEnd = (Date - 1) + #8:30:00 PM#
Else
gbldtStart = (Date - 2) + #8:30:00 PM#
gbldtEnd = (Date - 1) + #8:30:00 PM#
End If
' This Macro runs all necessary data for the Gate reports, in the format required
' and automatically adjusts for the week day ie. Tuesday-Thursday, and Thursday through Sunday Night.

DoCmd.Echo True, "Macros are running, please wait..."
DoCmd.OpenQuery "IRC_COMP_qry", acNormal, acEdit
DoCmd.OutputTo acQuery, "IRC_COMP_qry", "MicrosoftExcel(*.xls)", "\\SB-B01-W02\DATA\MFG_INFO\SHARED\FactoryRpt\Support Files\rptCompletions.xls", False, ""
DoCmd.Close acQuery, "IRC_COMP_qry"
DoCmd.OpenQuery "IRC_GATE_WIP", acNormal, acEdit
DoCmd.OutputTo acQuery, "IRC_GATE_WIP", "MicrosoftExcel(*.xls)", "\\SB-B01-W02\DATA\MFG_INFO\SHARED\FactoryRpt\Support Files\rptItem.xls", False, ""
DoCmd.Close acQuery, "IRC_GATE_WIP"
DoCmd.OpenQuery "IRC_GATE5_NC", acNormal, acEdit
DoCmd.OutputTo acQuery, "IRC_GATE5_NC", "MicrosoftExcel(*.xls)", "\\SB-B01-W02\DATA\MFG_INFO\SHARED\FactoryRpt\Support Files\rptNC.xls", False, ""
DoCmd.Close acQuery, "IRC_GATE5_NC"
DoCmd.OpenQuery "IRC_ITEM_RWRK_WIP", acNormal, acEdit
DoCmd.OutputTo acQuery, "IRC_ITEM_RWRK_WIP", "MicrosoftExcel(*.xls)", "\\SB-B01-W02\DATA\MFG_INFO\SHARED\FactoryRpt\Support Files\rptRework.xls", False, ""
DoCmd.Close acQuery, "IRC_ITEM_RWRK_WIP"
DoCmd.OpenQuery "IRC_New Starts", acNormal, acEdit
DoCmd.OutputTo acQuery, "IRC_New Starts", "MicrosoftExcel(*.xls)", "\\SB-B01-W02\DATA\MFG_INFO\SHARED\FactoryRpt\Support Files\rptNewStatus.xls", False, ""
DoCmd.Close acQuery, "IRC_New Starts"
Dim objxl As Object, x
'On Error Resume Next
'Leave this On Error line commented out for the moment so you can see any errors

Set objxl = GetObject("file://SB-B01-W02/DATA/MFG_INFO/SHARED/FactoryRpt/B8Dewar.xls")
objxl.Application.Visible = True
'objxl.ThisWorkbook.Visible = True
With objxl.Window
.Visible
End With
objxl.Sheets.Visible = True
objxl.Visible = True
'objxl.ActiveWorkbook.Run "update"
x = objxl.Application.Run("B8Dewar.xls!update")
objxl.Close
Set objxl = Nothing


IRCautodata_Exit:
Exit Function

IRCautodata_Err:
MsgBox Error$
Resume IRCautodata_Exit

End Function

 

[Yogi Anand]

Hi Deborah:

I suggest you also provide a link to this post in MrExcel's Microsoft Access Forum -- so Access enthusiasts can also look at it!