?
Solved

open excel file from code

Posted on 2016-08-17
6
Medium Priority
?
97 Views
Last Modified: 2016-08-17
excel 2010 vba

I have a command button on a form to open excel files.
I need it revamped to: Only open a specific excel file name(provided in code) from a specific folder on a server

MyFile.xls
or
MyFile.xlsx



10.xx.xxx.xx\c:\mydata\folderTemp


Sub XET_OpenAllXfilesInFolder()
    fldr = GetFolder(ActiveWorkbook.Path)
    xfile = Dir(fldr & "\*.xlsx")
    Do While xfile <> ""
        Workbooks.Open Filename:=fldr & "\" & xfile
    Loop
End Sub
0
Comment
Question by:Fordraiders
  • 4
  • 2
6 Comments
 
LVL 2

Accepted Solution

by:
Martin Andrews earned 2000 total points
ID: 41760001
You simply need to use the open function with the hardcoded values for fpath and fname

Dim owb As Workbook
owb = Workbooks.Open(fpath & "\" & fname)

Open in new window

0
 
LVL 3

Author Comment

by:Fordraiders
ID: 41760018
getting object variable with block varaiable not set.
0
 
LVL 3

Author Comment

by:Fordraiders
ID: 41760035
Dim sPath As String
Dim sFile As String
Dim sDir As String
Dim oLB As Workbook
Dim i1 As Long
Dim iMax As Long
 
sPath = "C:\Program Files\prise\iuom\"
sDir = FileSystem.Dir(sPath & "Version_iUom_Enterprise.xls", vbNormal)
 
    Set oLB = Workbooks.Open(sPath & sDir)


This working with no errors but i do not see the workbook.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Author Comment

by:Fordraiders
ID: 41760044
Workbooks.Application.Visible = true
0
 
LVL 3

Author Closing Comment

by:Fordraiders
ID: 41760045
Thanks
0
 
LVL 2

Expert Comment

by:Martin Andrews
ID: 41760050
If your original code was working ng, you can simply use that, the point is that you simply set it manually in your code rather than letting the user select it:

Dim own as Workbook
Workbooks.Open Filename:=fpath & "\" & fname
Set owb = ActiveWorkbook

Open in new window

0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question