Go Back   Access World Forums > Apps and Windows > Visual Basic

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-19-2018, 09:45 PM   #31
MITSupport
Newly Registered User
 
Join Date: Aug 2016
Posts: 46
Thanks: 2
Thanked 0 Times in 0 Posts
MITSupport is on a distinguished road
Re: Coding Help please

Hi BigHappyDaddy

This is where I inserted the line
Code:
   Set wApp = New Word.Application
    MsgBox "Directory:" & fn
    Set wDoc = wApp.Documents.Open(fn)
It still errors out on the line following saying invalid Directory.

Thanks for your help

Allan

MITSupport is offline   Reply With Quote
Old 02-19-2018, 09:52 PM   #32
BigHappyDaddy
Coding Monkey Wanna-Be
 
BigHappyDaddy's Avatar
 
Join Date: Aug 2012
Location: Puyallup, WA
Posts: 205
Thanks: 6
Thanked 38 Times in 37 Posts
BigHappyDaddy is on a distinguished road
Re: Coding Help please

But what was printed out on the message box? It should be the file path of the file, but I suspect it is not.

Sent from my SM-G900P using Tapatalk
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
BigHappyDaddy is offline   Reply With Quote
Old 02-19-2018, 10:10 PM   #33
MITSupport
Newly Registered User
 
Join Date: Aug 2016
Posts: 46
Thanks: 2
Thanked 0 Times in 0 Posts
MITSupport is on a distinguished road
Re: Coding Help please

Yes mate it did come up with the directory

MITSupport is offline   Reply With Quote
Old 02-22-2018, 06:24 AM   #34
BigHappyDaddy
Coding Monkey Wanna-Be
 
BigHappyDaddy's Avatar
 
Join Date: Aug 2012
Location: Puyallup, WA
Posts: 205
Thanks: 6
Thanked 38 Times in 37 Posts
BigHappyDaddy is on a distinguished road
Re: Coding Help please

Directory only, or did it include the file name?
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
BigHappyDaddy is offline   Reply With Quote
Old 02-22-2018, 05:10 PM   #35
MITSupport
Newly Registered User
 
Join Date: Aug 2016
Posts: 46
Thanks: 2
Thanked 0 Times in 0 Posts
MITSupport is on a distinguished road
Re: Coding Help please

It posted the directory only.

I have been trying to use the environ statement as well:

Original Code:
Code:
 fn = "C:\QA\Templates" & fn
 Set wApp = New Word.Application
    Set wDoc = wApp.Documents.Open(fn)
Test EnvironCode:
Code:
  fn = Environ("SystemDrive") & "QA\Templates" & fn
    
    Set wApp = New Word.Application
    Set wDoc = wApp.Documents.Open(fn)
Even with this code structure the database still comes up with:
Run-time error'5455':
The directory name isn't valid.
(C:\QA\Templates\)

and then on debug it highlights the
Code:
 Set wDoc = wApp.Documents.Open(fn)
Am i doing something wrong? I really cant find an answer for this conundrum and it is driving me crazy

Thanks for the help guys i really appreciate it

Allan
MITSupport is offline   Reply With Quote
Old 02-22-2018, 05:16 PM   #36
BigHappyDaddy
Coding Monkey Wanna-Be
 
BigHappyDaddy's Avatar
 
Join Date: Aug 2012
Location: Puyallup, WA
Posts: 205
Thanks: 6
Thanked 38 Times in 37 Posts
BigHappyDaddy is on a distinguished road
Re: Coding Help please

What exactly is in fn at the open command?

Sent from my SM-G900P using Tapatalk
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
BigHappyDaddy is offline   Reply With Quote
Old 02-22-2018, 05:20 PM   #37
MITSupport
Newly Registered User
 
Join Date: Aug 2016
Posts: 46
Thanks: 2
Thanked 0 Times in 0 Posts
MITSupport is on a distinguished road
Re: Coding Help please

It should be the complete file location:
Code:
Public Sub PrintReport(ReportType As String)

    Dim wApp As Word.Application
    Dim wDoc As Word.Document
    Dim fn As String
        
    Select Case ReportType
        Case "Power"
            fn = "Core Power Cable.dotx"
        Case "Control"
            fn = "Core COntrol Cable.dotx"
        Case "Earth"
            fn = "Core Earth Test Sheet.dotx"
        Case "Motor"
            fn = "Core Test Motor Test Sheet.dotx"
        Case "Instrument"
            fn = "Core Instrument Test Sheet.dotx"
        Case "ITP"
            fn = "Core Earth Test Sheet.dotx"
        Case "Single Phase"
            fn = "Core Single Phase Power Cable.dotx"
        Case "Three Phase"
            fn = "Core Three Phase Power Cable.dotx"
        Case "IS"
            fn = "Core Intrinsically Safe Cable.dotx"
    End Select
        
    fn = Environ("SystemDrive") & "QA\Templates" & fn
    
    Set wApp = New Word.Application
    Set wDoc = wApp.Documents.Open(fn)
So it should be opening "C:\QA\Templates\ and any of the files that were mentioned in the Case argument

I hope that this makes sense to you.

Thanks again

Allan

MITSupport is offline   Reply With Quote
Old 02-22-2018, 05:48 PM   #38
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,289 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
No. Evaluate this...
Code:
? Environ("SystemDrive") & "QA\Templates"
 C:QA\Templates
That is not a valid folder spec.
Code:
    fn = Environ("SystemDrive") & "\QA\Templates\" & fn
See in red...?
This is what BigHappy has been saying with the MsgBox for the last few posts, check the value of fn. Is it a valid directory?
Quote:
msgbox "Directory:" & fn
Test that value. Is it a valid path?
Mark
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 02-22-2018, 06:00 PM   #39
MITSupport
Newly Registered User
 
Join Date: Aug 2016
Posts: 46
Thanks: 2
Thanked 0 Times in 0 Posts
MITSupport is on a distinguished road
Re: Coding Help please

Hi Guys

I have put the msgbox code back into the routine above the line that was erroring out on:
Code:
fn = Environ("SystemDrive") & "\QA\Templates\"
    
    Set wApp = New Word.Application
    MsgBox "Directory:" & fn
    Set wDoc = wApp.Documents.Open(fn)
When it runs it pops up a message box with the just the directory name:

Directory:C:\QA\Templates\

The directory exists and it doesn't show the file that it is supposed to be using just the directory.

I thought the fn from the CASE argument was also supposed to be populated as well. I have a feeling that i am wrong with that idea.

Does this make sense to you because it doesn't make sense to me

Allan
MITSupport is offline   Reply With Quote
Old 02-22-2018, 06:31 PM   #40
MITSupport
Newly Registered User
 
Join Date: Aug 2016
Posts: 46
Thanks: 2
Thanked 0 Times in 0 Posts
MITSupport is on a distinguished road
Re: Coding Help please

Hi Guys

This is a snip from the the code that is msgbox section.

I am so sorry for being so dumb in vba (it has been a very long time since i have done anything with access vba)

Thanks for all your help. I really appreciate it

Allan
Attached Images
File Type: png Test.PNG (6.6 KB, 18 views)
MITSupport is offline   Reply With Quote
Old 02-22-2018, 06:58 PM   #41
MITSupport
Newly Registered User
 
Join Date: Aug 2016
Posts: 46
Thanks: 2
Thanked 0 Times in 0 Posts
MITSupport is on a distinguished road
Re: Coding Help please

Hi Guys

I have been playing and created a button with the following code:

Code:
Sub RetriveFolder()
 
    Dim myfolder As String
    Dim Fldr As String
    
    myfolder = "C:\QA\Templates\"
    Fldr = Dir(myfolder, vbDirectory)
    
    If Len(Fldr) > 0 Then
      MsgBox (Fldr & " Already Exists")
    Else
      MkDir myfolder
      MsgBox ("Folder Created")
    End If
    
End Sub
When this runs it replys with "Already Exists"

now when we run the PrintReport routine it still errors out on the :

Code:
 Set wDoc = wApp.Documents.Open(fn)
What am i doing wrong

Allan
MITSupport is offline   Reply With Quote
Old 02-22-2018, 07:49 PM   #42
BigHappyDaddy
Coding Monkey Wanna-Be
 
BigHappyDaddy's Avatar
 
Join Date: Aug 2012
Location: Puyallup, WA
Posts: 205
Thanks: 6
Thanked 38 Times in 37 Posts
BigHappyDaddy is on a distinguished road
Re: Coding Help please

If this is your code, you are missing a slash after "Templates". See below.

fn = Environ("SystemDrive") & "QA\Templates" & fn

Sent from my SM-T530NU using Tapatalk
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
BigHappyDaddy is offline   Reply With Quote
Old 02-22-2018, 07:51 PM   #43
BigHappyDaddy
Coding Monkey Wanna-Be
 
BigHappyDaddy's Avatar
 
Join Date: Aug 2012
Location: Puyallup, WA
Posts: 205
Thanks: 6
Thanked 38 Times in 37 Posts
BigHappyDaddy is on a distinguished road
Re: Coding Help please

Sorry, my post was way late!

Sent from my SM-T530NU using Tapatalk
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
BigHappyDaddy is offline   Reply With Quote
Old 02-22-2018, 08:03 PM   #44
MITSupport
Newly Registered User
 
Join Date: Aug 2016
Posts: 46
Thanks: 2
Thanked 0 Times in 0 Posts
MITSupport is on a distinguished road
Re: Coding Help please

Hi Guys

I have worked it out to a point. Here is the revised code that is working.

Code:
Option Compare Database
Option Explicit

Public Sub PrintReport(ReportType As String)

    Dim wApp As Word.Application
    Dim wDoc As Word.Document
    Dim fn As String
        
    Select Case ReportType
        Case "Power"
            fn = "C:\QA\Templates\Core Power Cable.dotx"
        Case "Control"
            fn = "C:\QA\Templates\Core COntrol Cable.dotx"
        Case "Earth"
            fn = "C:\QA\Templates\Core Earth Test Sheet.dotx"
        Case "Motor"
            fn = "C:\QA\Templates\Core Test Motor Test Sheet.dotx"
        Case "Instrument"
            fn = "C:\QA\Templates\Core Instrument Test Sheet.dotx"
        Case "ITP"
            fn = "C:\QA\Templates\Core Earth Test Sheet.dotx"
        Case "Single Phase"
            fn = "C:\QA\Templates\Core Single Phase Power Cable.dotx"
        Case "Three Phase"
            fn = "C:\QA\Templates\Core Three Phase Power Cable.dotx"
        Case "IS"
            fn = "C:\QA\Templates\Core Intrinsically Safe Cable.dotx"
    End Select
        
    ' fn = Environ("SystemDrive") & "\QA\Templates\"
            
    Set wApp = New Word.Application
    ' MsgBox "Directory:" & fn
    Set wDoc = wApp.Documents.Open(fn)
        
    With CurrentDb.OpenRecordset("qryReports")
        Do While Not .EOF
            wDoc.Bookmarks("Project").Range.Text = Nz(!Project, "")
            wDoc.Bookmarks("Location").Range.Text = Nz(!Location, "")
            wDoc.Bookmarks("CableNumber").Range.Text = Nz(!Prefix, !Type)
            wDoc.Bookmarks("Origin").Range.Text = Nz(!OriginZone, "")
            wDoc.Bookmarks("Dest").Range.Text = Nz(!DestinationZone, "")
            wDoc.Bookmarks("Date").Range.Text = Nz(!DateChecked, "")
            wDoc.Bookmarks("CheckedBy").Range.Text = Nz(!CheckedBy, "")
            wDoc.Bookmarks("Comments").Range.Text = Nz(!Comments, "")
            wDoc.Bookmarks("Tool").Range.Text = Nz(!Certification, "")
        
            wDoc.Bookmarks("Project").Range.Delete wdCharacter, Len(Nz(!Project, ""))
            wDoc.Bookmarks("Location").Range.Delete wdCharacter, Len(Nz(!Location, ""))
            wDoc.Bookmarks("CableNumber").Range.Delete wdCharacter, Len(Nz(!Prefix, !Type))
            wDoc.Bookmarks("Origin").Range.Delete wdCharacter, Len(Nz(!OriginZone, ""))
            wDoc.Bookmarks("Dest").Range.Delete wdCharacter, Len(Nz(!DestinationZone, ""))
            wDoc.Bookmarks("Date").Range.Delete wdCharacter, Len(Nz(!Date, ""))
            wDoc.Bookmarks("CheckedBy").Range.Delete wdCharacter, Len(Nz(!CheckedBy, ""))
            wDoc.Bookmarks("Comments").Range.Delete wdCharacter, Len(Nz(!Comments, ""))
            wDoc.Bookmarks("Tool").Range.Delete wdCharacter, Len(Nz(!Certification, ""))
            
            .MoveNext
        Loop
        .Close
    End With
    
    wDoc.Close True
    wApp.Quit
            
End Sub
Now the problem has arisen in regards to the bookmark fields.

When the code is run it goes through and errors out on the first line of the bookmark coding.

The error is:
Run-time error '91':
Object variable or With block variable not set

In this instance was i supposed to define the variables for the bookmarks?

Thanks for all your help guys

Allan
MITSupport is offline   Reply With Quote
Old 02-22-2018, 08:07 PM   #45
BigHappyDaddy
Coding Monkey Wanna-Be
 
BigHappyDaddy's Avatar
 
Join Date: Aug 2012
Location: Puyallup, WA
Posts: 205
Thanks: 6
Thanked 38 Times in 37 Posts
BigHappyDaddy is on a distinguished road
Re: Coding Help please

Post your code in its entirety. I am seeing a lot of inconsistencies in your code snippets. I understand what you want, but I am a little lost in exactly how you are trying to accomplish it. I am certain you have a small typo or omission somewhere in your code.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
BigHappyDaddy is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA Coding Prayder Modules & VBA 4 03-28-2014 03:23 PM
Need some help with coding Marinus Forms 10 03-22-2011 04:47 AM
Coding help please... WSC Modules & VBA 2 11-20-2006 10:53 AM
Which is a better way of coding? yhgtbfk General 6 10-18-2004 05:21 AM
VBA Coding StefanSch Forms 7 04-14-2003 08:52 AM




All times are GMT -8. The time now is 07:15 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World