How to open Excel file using VB?

Part of my VB program is written with :

'(1) open Excel files
   currentDate = Format(Date, "YYYY") & " " & Format(Date, "MM") & " " & Format(Date, "DD") & " " & "0000" & " " & "(WIDE)"
   filePath = "C:\nbutane\rsview\DLGLOG\RSVIEW\" & currentDate & ".dbf"


When the VB executes, it'll open the file called      2001 11 23 0000 (Wide)    with Excel.

My problem is when the file name changed to      2001 11 23 0001 (Wide)     , the VB can't access it because it's reading from '0000'. Sometimes the file will change to '0002' .... and so on.

Please help. Thanks.
ckchew666Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pratap_rCommented:
are you looking for a dynamic way of finding if a file exists? for example 2001 11 23 0001 (Wide).dbf in your case?

0
pratap_rCommented:
try this, the GetFileName funtion returns you the file that exists on the disk, you might want to change the counters accordingly

Private Sub Form_Load()
MsgBox GetFileName
End Sub

Public Function GetFileName()
On Error Resume Next
Dim str1$, str2$, ln%
str1 = Format(Date, "YYYY") & " " & Format(Date, "MM") & " " & Format(Date, "DD") & " "
For i = 0 To 99
    str2 = str1 + Format(i, "0000") & " " & "(WIDE)"
    filepath = "C:\nbutane\rsview\DLGLOG\RSVIEW\" & str2 & ".dbf"
    ln = FileLen(filepath)
    If Err.Number <> 53 Then
        GetFileName = filepath
        Exit Function
    End If
    Err.Clear
Next
End Function


Have Fun!
Pratap
0
ckchew666Author Commented:
I want my VB program to be able to find 2001 11 23 0001 (Wide).dbf  and open it when 2001 11 23 0000 (Wide).dbf  does not exists
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

pratap_rCommented:
thats what the GetFileName returns, it starts checking from 2004 11 23 0000 (Wide).dbf to 2004 11 23 0099 (Wide).dbf and returns on the first found file

so if 2004 11 23 0000 (Wide).dbf does not exist and 2004 11 23 0001 (Wide).dbf exist then thats what the function call will return.. the fullpath to 2004 11 23 0001 (Wide).dbf


Have Fun!
Pratap
0
ckchew666Author Commented:
SOrry, I'm very new to VB, I don't really understand ur sample codes, tho` it might be very easy for you :)

Looks like ur VB sample is able to search for file with number 0000 to 0099. Il'll keep searching until it found a number, am i right? But how do I integrate it to my VB codes?

Here's my entire VB program.

_____________________________________________________________________________



Public Sub btnCreateExcel_Click()
On Error GoTo Err_btnCreateExcel_Click

   Dim objExcel As Object
   Dim objExcel_data As Object
   Dim currentDate As String
   Dim wbTarget As Object
   Dim wbSource As Object
   Dim filePath As String
     

'(1) open Excel files
   currentDate = Format(Date, "YYYY") & " " & Format(Date, "MM") & " " & Format(Date, "DD") & " " & "0000" & " " & "(WIDE)"
   filePath = "C:\nbutane\rsview\DLGLOG\RSVIEW\" & currentDate & ".dbf"

'(2) open the source workbook as read only and open target workbook with write
   Set objExcel_data = CreateObject("Excel.Application")
   Set wbSource = objExcel_data.Workbooks.Open(filePath).Sheets(currentDate)

   Set objExcel = CreateObject("Excel.Application")
   Set wbTarget = objExcel.Workbooks.Open("C:\nbutane\rsview\report\" & "FC Daily Report" & ".xls").Sheets(1)

     



'(3) extract data from source and put into target
    With wbSource
        '******************************************
        '''''''''''(Rows, Column)''''''''''''''''''
        '******************************************
        Dim i As Integer
        i = 2 'always start with 2 base on the source sheet
        Do While (wbSource.cells(i, 2) <> "")               ' do below if something
           
           
           
  If CDate(.cells(i, 2).Value) >= CDate("6:00:00") And CDate(.cells(i, 2).Value) <= CDate("6:02:00") Then
               wbTarget.cells(5, 3).Value = .cells(i, 5).Formula
               wbTarget.cells(5, 4).Value = .cells(i, 7).Formula
               wbTarget.cells(5, 5).Value = .cells(i, 9).Formula
               wbTarget.cells(5, 6).Value = .cells(i, 11).Formula
               wbTarget.cells(5, 7).Value = .cells(i, 13).Formula
               wbTarget.cells(5, 8).Value = .cells(i, 15).Formula
               wbTarget.cells(5, 9).Value = .cells(i, 17).Formula
                                     
                             
  ElseIf CDate(.cells(i, 2).Value) >= CDate("5:00:00") And CDate(.cells(i, 2).Value) <= CDate("5:02:00") Then
               wbTarget.cells(28, 3).Value = .cells(i, 5).Formula
               wbTarget.cells(28, 4).Value = .cells(i, 7).Formula
               wbTarget.cells(28, 5).Value = .cells(i, 9).Formula
               wbTarget.cells(28, 6).Value = .cells(i, 11).Formula
               wbTarget.cells(28, 7).Value = .cells(i, 13).Formula
               wbTarget.cells(28, 8).Value = .cells(i, 15).Formula
               wbTarget.cells(28, 9).Value = .cells(i, 17).Formula
           
           End If
           i = i + 1   'go to next row
       Loop            'stop when no more data
   End With

'(4) save the target excel file and close both the target and source.
   wbSource.Application.Windows(currentDate & ".dbf").Visible = True
   wbSource.Application.ActiveWorkbook.Close
   objExcel_data.Quit


  'set target path
   wbTarget.Application.Windows("FC Daily Report.xls").Visible = True
   
  'to print out the source report
   objExcel.Workbooks(1).PrintOut Copies:=1, Collate:=True
   
  'after target is printed, save, close and exit Excel
   wbTarget.Application.ActiveWorkbook.Save
   wbTarget.Application.ActiveWorkbook.Close
   objExcel.Quit

   Set wbSource = Nothing  'free memory
   Set wbTarget = Nothing

     
   'Summary Excel File to be viewable (temporary not in use)
'   Set objExcel = CreateObject("Excel.Application")

   '(temporary not in use)
'   objExcel.Workbooks.Open ("C:\nbutane\rsview\report\FC Daily Report.xls")
     
   'open summary file with Excel (temporary not in use)
'   objExcel.Application.Visible = True
     
   
Err_btnCreateExcel_Click:
   'only display message when error encountered
   If Err.Number <> 0 Then
       MsgBox Err.Description
   End If

End Sub
__________________________________________________________________________

Hope you can show me. Thanks.
0
pratap_rCommented:
sorry ckchew.. my mistake, i should have explained the code... but you got it right.. thats what the code does..

hmm now coming to changing your code.. change your line

'(1) open Excel files
   currentDate = Format(Date, "YYYY") & " " & Format(Date, "MM") & " " & Format(Date, "DD") & " " & "0000" & " " & "(WIDE)"
   filePath = "C:\nbutane\rsview\DLGLOG\RSVIEW\" & currentDate & ".dbf"
'(2) open the source workbook as read only and open target workbook with write


to

'(1) open Excel files
On Error Resume Next
Dim ln%
For i = 0 To 99
   currentDate = Format(Date, "YYYY") & " " & Format(Date, "MM") & " " & Format(Date, "DD") & " " & Format(i,"0000") & " " & "(WIDE)" 'notice the Format(i, "0000")
    filepath = "C:\nbutane\rsview\DLGLOG\RSVIEW\" & currentDate & ".dbf"
    ln = FileLen(filepath) 'a dummy function call to get the filesize, not that we are going to use it anywhere.. just to raise an error if the file does not exist
    If Err.Number <> 53 Then 'err.number is set to 53 if we get a file not found error
        GetFileName = filepath 'so the error was not 53 so we assume the file is present
        exit for 'come out of the for loop
    End If
    Err.Clear '<--clear the previous error
Next
on error goto 0 '<-- this will disable the resume next error handling that we enabled earlier in the code
'(2) open the source workbook as read only and open target workbook with write


let me know if you face any problems :-)

Have Fun!
Pratap
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ckchew666Author Commented:
Hi Pratap,

You got it work!! You are da` man!
This is the fastest guide I've ever get. Great job! :)

For i = 0 To 99
 This one I've change it to p (as a token of appreciation p = pratap), because i is already defined in the program.

Thanks again.

CK Chew
0
pratap_rCommented:
my pleasure :-D

Have Fun!
Pratap
0
ShobhitCommented:
You should use "ShellExecute" API to open an Excel File. Accept the path of the excel file and pass this parameter to the ShellExecute API function.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.