Consulting

Page 2 of 4 FirstFirst 1 2 3 4 LastLast
Results 21 to 40 of 73

Thread: Solved: Excel to access cell for cell import

  1. #21
    Hi Charlize

    Really need your advise on this one please...
    If i wanted to put the Excel file on a remote network machine and have the database on my machine how would i do that in the coding?

    (As above)

    Many thanks again for all your help...

  2. #22
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    Instead of using the Activeworkbook.Path, use the network path

    [vba]

    MyPath = "\\myserver\myShare" & "\DataStore.mdb"
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #23
    Hi there

    I use this code and it gives me a "Expected expression" error

    [VBA]Set oConn = CreateObject("ADODB.Connection")
    oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source= "\\10.0.1.3\\ABI" & "\DataStore.mdb & ActiveWorkbook.Path & "\DataStore.mdb"[/VBA]

  4. #24
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    Instead of, not as well as

    [vba]

    Set oConn = CreateObject("ADODB.Connection")
    oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source= "\\10.0.1.3\AB\DataStore.mdb"
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #25
    Its really not liking the "\\" part of the statement... any suggestions?

  6. #26
    Still giving an "Expression exprexcted error" while compiling...

  7. #27
    VBAX Master
    Joined
    Jul 2006
    Location
    Belgium
    Posts
    1,284
    Location
    Can you map the directory of the server where the database resides to a driveletter. What I mean is this :

    1. \\10.0.1.3\AB mapping as drive Z: (but everybody must have permissions to that directory on the server. So the IT guys/girls do this on the server so that driveletter Z: points to that directory for everybody on the network.
    2. In your coding use Z:\Datastore instead

    Just an idea.

  8. #28
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    If you have that networked drive mapped to a drive letter on your machine, try this routine to get the UNC path

    [vba]


    Declare Function WNetGetConnection32 Lib "MPR.DLL" Alias "WNetGetConnectionA" ( _
    ByVal lpszLocalName As String, _
    ByVal lpszRemoteName As String, _
    lSize As Long) As Long

    Const NO_ERROR As Long = 0
    Const lBUFFER_SIZE As Long = 255

    Function GetUNCPath(Driveletter As String) As String
    Dim mpRemoteName As String
    Dim lSize As Long
    Driveletter = Driveletter & ":"
    mpRemoteName = mpRemoteName & Space(lBUFFER_SIZE)
    If WNetGetConnection32(Driveletter, _
    mpRemoteName, _
    lBUFFER_SIZE) = NO_ERROR Then
    GetUNCPath = mpRemoteName
    End If
    End Function
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #29
    Thanks for your help again Charlize
    Once again you have proved yourself ;-)

  10. #30
    Thanks for your help again Charlize
    Once again you have proved yourself ;-)


  11. #31
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    Oops, I think I see it

    [vba]

    Set oConn = CreateObject("ADODB.Connection")
    oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=\\10.0.1.3\AB\DataStore.mdb"
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  12. #32
    VBAX Master
    Joined
    Jul 2006
    Location
    Belgium
    Posts
    1,284
    Location
    xld, thanks for that nice piece of coding to get the path of a mapped directory.

    White Nova, perhaps that your server has a name so that you can't refer to it with ip adresses but use the name of the server. If your server has the name server01 you could use \\server01. If AB is a user it is probably located under the users directory. So \\server01\users\AB will get you to that users directory.

    I could be wrong off course.

  13. #33
    Sounds better, but wehn running gives a path error, i have checked it!!!
    Would like it this way though, would save time on the drive mapping idea...

  14. #34
    VBAX Master
    Joined
    Jul 2006
    Location
    Belgium
    Posts
    1,284
    Location
    Quote Originally Posted by White_Nova
    Only three this time ???

  15. #35
    I would like to try xld's way but would like to know where i need to put that piece of coding he has provided above [VBA]Declare Function WNetGetConnection32 Lib "MPR.DLL" Alias "WNetGetConnectionA" ( _
    ByVal lpszLocalName As String, _
    ByVal lpszRemoteName As String, _
    lSize As Long) As Long

    Const NO_ERROR As Long = 0
    Const lBUFFER_SIZE As Long = 255

    Function GetUNCPath(Driveletter As String) As String
    Dim mpRemoteName As String
    Dim lSize As Long
    Driveletter = Driveletter & ":"
    mpRemoteName = mpRemoteName & Space(lBUFFER_SIZE)
    If WNetGetConnection32(Driveletter, _
    mpRemoteName, _
    lBUFFER_SIZE) = NO_ERROR Then
    GetUNCPath = mpRemoteName
    End If
    End Function [/VBA]

  16. #36
    Sorry Charlize, fingers are sore from trying to get this right!!!!

  17. #37
    VBAX Master
    Joined
    Jul 2006
    Location
    Belgium
    Posts
    1,284
    Location
    [VBA]Option Explicit
    Declare Function WNetGetConnection32 Lib "MPR.DLL" Alias "WNetGetConnectionA" ( _
    ByVal lpszLocalName As String, _
    ByVal lpszRemoteName As String, _
    lSize As Long) As Long

    Const NO_ERROR As Long = 0
    Const lBUFFER_SIZE As Long = 255

    Sub test_path()
    MsgBox "Z = " & GetUNCPath("Z")
    End Sub
    Function GetUNCPath(Driveletter As String) As String
    Dim mpRemoteName As String
    Dim lSize As Long
    Driveletter = Driveletter & ":"
    mpRemoteName = mpRemoteName & Space(lBUFFER_SIZE)
    If WNetGetConnection32(Driveletter, _
    mpRemoteName, _
    lBUFFER_SIZE) = NO_ERROR Then
    GetUNCPath = mpRemoteName
    End If
    End Function[/VBA]

  18. #38
    VBAX Master
    Joined
    Jul 2006
    Location
    Belgium
    Posts
    1,284
    Location
    Quote Originally Posted by White_Nova
    Much better ...

  19. #39
    I once again need your help please!!!

    I have the access database with data inside it, i now have an excel template and would like to have the answers for the template produced from Access... how on earth do i do that???

    Please help????

  20. #40
    VBAX Master
    Joined
    Jul 2006
    Location
    Belgium
    Posts
    1,284
    Location
    There are two ways you could do it.

    1. The easy one is just getting every record (which you already have) and filter them when they are present in excel (but could cost extra time when there are a lot of records).
    2. Building a selectif selection based on the where clausule. You'll have to check if you filled something in for every field. Depending on this your where section will differ.
    Then you've got also 'or' and 'and' as you define the where's (where mydate = x and person = x . where mydate = x or person = x).

    Also take a look at this site to see what kind of expressions are possible ... http://sqlcourse2.com/select2.html
    Last edited by Charlize; 11-13-2007 at 03:43 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •