Solved: Excel to access cell for cell import

[White_Nova]

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...

[Bob Phillips]

Instead of using the Activeworkbook.Path, use the network path

[vba]

MyPath = "\\myserver\myShare" & "\DataStore.mdb"
[/vba]

[White_Nova]

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]

[Bob Phillips]

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]

[White_Nova]

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

[White_Nova]

Still giving an "Expression exprexcted error" while compiling...

[Charlize]

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.

[Bob Phillips]

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]

[White_Nova]

Thanks for your help again Charlize
Once again you have proved yourself ;-)

[White_Nova]

Thanks for your help again Charlize
Once again you have proved yourself ;-)

[Bob Phillips]

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]

[Charlize]

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.

[White_Nova]

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...

[Charlize]

Only three this time ???

[White_Nova]

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]

[White_Nova]

Sorry Charlize, fingers are sore from trying to get this right!!!!

[Charlize]

[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]

[Charlize]

Much better ...

[White_Nova]

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????

[Charlize]

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