| Author |
Messages |
|
Mark Pajak
Posts:6
 |
| 14-04-2010 9:51 PM |
 Alert
|
Hi,
I work for the Natural History department of Bristol Museum, UK. I have just finished writing a fairly complex report in VB to extract specimen dimensions and info from the events module into a list in excel to email to the case designers.
I have created a vba report that uses data from several .csv files liked using two seperate queries in Excel. It works fine when I run the report from my computer, and usually from others on the network. The problem is that when another user runs the report it doesnt always find the latest export of the .csv fiels to report on.
The report uses a dynamic file path string to insert the user login into the filepath (C:\Documents and Settings\[Your username]\Local Settings\Application Data\KESoftware\Reports\ecatalogue)
The problem is that the .csv files are not always exported here - sometimes they go into strange places like temporary personal folders or something - these useually have the user name but with a suffix .AD or ~AD
......is there a way to consistently retrive the filepath to the .csv files to locate the most recent .csv exports?
Thanks,
Mark
|
|
------------------------------------------------------------------------
Mark Pajak |
|
|
JP Brown
Posts:26
|
| 14-04-2010 11:50 PM |
Alert
|
Dear Mark
1. The weird suffixes are typically because a user's login domain has been changed.
2. How are you getting the path? In VB .NET I'm guessing it would look something like this:
Dim path as string = Environment.GetFolderPath(Environment.SpecialFolder.Personal) & @"\Local Settings\Application Data\KESoftware\Reports\ecatalogue"
Best
JP |
|
|
|
|
JP Brown
Posts:26
|
| 15-04-2010 12:03 AM |
Alert
|
I just RTM for VB, and it looks like, if the folder is on the local machine
Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) & @"\KESoftware\Reports\ecatalogue"
would be neater.
http://msdn.microsoft.com/en-us/library/system.environment.specialfolder.aspx
Cheers
JP |
|
|
|
|
Mark Pajak
Posts:6
|
| 15-04-2010 12:45 AM |
Alert
|
Thanks for getting back to me - that certainly looks like the answer, but I dont think I am using VB.Net, or at least I dont know how.
This is what I have currently:
Querypath1 = "ODBC;DSN=EMu Catalogue;DBQ=C:\Documents and Settings\"
Querypath2 = "\Local Settings\Application Data\KESoftware\Reports\ecatalogue;DefaultDir=C:\Documents and Settings\"
Querypath3 = "\Local Settings\Application Data\KESoftware\Reports\ecatalogue;DriverId=27;MaxBufferSize=204, " & _
";PageTimeout=5"
'Debug.Print Querypath1 & UserNameOffice & Querypath2
Dim QueryPath As String
QueryPath = Querypath1 & UserNameOffice & Querypath2 & UserNameOffice & Querypath3
Debug.Print QueryPath
'-------------------------
Worksheets("AllData").QueryTables(1).Connection = QueryPath
'-------------------------------
Function UserNameOffice() As String
UserNameOffice = Application.UserName
End Function
'-----------------------------------
Any ideas as to how to convert the above to use (Environment.SpecialFolder.LocalApplicationData) ??
Thanks again,
Mark
|
|
------------------------------------------------------------------------
Mark Pajak |
|
|
Mark Pajak
Posts:6
|
| 15-04-2010 2:15 AM |
Alert
|
Ok i have updated my original code to find the path to the [username]\Local Settings\application data folder.
It is working so far but I am not convinced that this fully resolves the problem of the .csv files getting exported to strange places with suffixes.
I think there is still a chance that EMu exported the .csvs to the weird folder but the report looked in a different folder and opened a previous export that had gone to the right place. It looks like the only way to check is to run around the museum logging on to different PCs as different people and running the report.
I used this to retrive the path to the folder:
'-------------------------------------------------------------------------------------------------------------------
Public Declare Function SHGetSpecialFolderLocation _
Lib "shell32" (ByVal hWnd As Long, _
ByVal nFolder As Long, ppidl As Long) As Long
Public Declare Function SHGetPathFromIDList _
Lib "shell32" Alias "SHGetPathFromIDListA" _
(ByVal Pidl As Long, ByVal pszPath As String) As Long
Public Declare Sub CoTaskMemFree Lib "ole32" (ByVal pvoid As Long)
Public Const CSIDL_PERSONAL = &H5
Public Const CSIDL_DESKTOPDIRECTORY = &H10
Public Const MAX_PATH = 260
Public Const CSIDL_LOCAL_APPDATA As Long = &H1C
Public Const NOERROR = 0
Public Function SpecFolder(ByVal lngFolder As Long) As String
Dim lngPidlFound As Long
Dim lngFolderFound As Long
Dim lngPidl As Long
Dim strPath As String
strPath = Space(MAX_PATH)
lngPidlFound = SHGetSpecialFolderLocation(0, lngFolder, lngPidl)
If lngPidlFound = NOERROR Then
lngFolderFound = SHGetPathFromIDList(lngPidl, strPath)
If lngFolderFound Then
SpecFolder = Left$(strPath, _
InStr(1, strPath, vbNullChar) - 1)
End If
End If
CoTaskMemFree lngPidl
End Function
'--------------------------------------------------------------------------------------------------------------
Thanks again for the help. |
|
------------------------------------------------------------------------
Mark Pajak |
|
|
Bernard Marshall
Posts:41
|
| 15-04-2010 8:19 AM |
Alert
|
Hello Mark,
If you are using VBA in excel you do not need to specify the path to the location of the database. You can use code like:
Worksheets("Data").QueryTables(1).Connection = "ODBC;DSN=EMu Loans;"
where you do not specify the DBQ. In this case ODBC will use the "DBQ" defined for the data source in the registry. If you want to look up the path the "easiest" way is to use the Windows registry. The path is defined for each data source under:
\\HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\dbname
where dbname is the name of the database (e.g. "EMu Loans" as specified in the connection string). Look for the DQB key.
Hope this helps.
Regards
bern. |
|
Bernard Marshall
KE Software
Melbourne, Australia |
|
|
Mark Pajak
Posts:6
|
| 15-04-2010 6:53 PM |
Alert
|
Hello Bern,
Thanks for your reply - I agree that it would be simpler not to specify the path, and doing it that way (as per the EMu help notes) did work initially but only on my PC. When the report was run by another user on another computer the code came up with the following error message:
ODBC Text Driver Login Failed
'C:\Documents and settings\BRLSAD2\Local Settings\Application Data\KESoftware\Reports\ecatalogue' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.
...where BRLSAD2 was the login name of the user that created the report and not the login of the person running the report.
so.... After looking through all of the code and not finding any reference to my personal foler, I used the macro recorder to re-run one of the queries and the resulting code DID have my login details:
ActiveWindow.SelectedSheets.Visible = False
Sheets("AllData").Visible = True
Range("C1").Select
With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DSN=EMu Catalogue;DBQ=C:\Documents and Settings\BRLSMP4\Local Settings\Application Data\KESoftware\Reports\ecatalogue;DefaultDi" _
), Array( _
"r=C:\Documents and Settings\BRLSMP4\Local Settings\Application Data\KESoftware\Reports\ecatalogue;DriverId=27;MaxBufferSize=204" _
), Array(";PageTimeout=5;"))...................etc
So that was when i decided to create a dynamic pathe to the home folder. It seems that by creating the links in MS Query for the report there somewhere lies a hidden link back to the users PC that does not change when another report is run.
If I am wrong and there is a simpler way then I would definitely use it, perhaps this might be as our museum runs Escel 2000 or something?
Thanks,
Mark
|
|
------------------------------------------------------------------------
Mark Pajak |
|
|
Michael Elliott
Posts:2
|
| 28-07-2010 11:22 AM |
Alert
|
Hi Mark,
From what I understand, Emu puts the csv files in the Documents and Settings folder of the Windows user, not the Emu user. So if the "Your Username" part of your path is asking for the Emu username and not that of the Windows user, you will have trouble finding the files. For example if someone called angelina.jolie is logged into windows, but the emu user is brad.pitt, your code will fail.
I create most of our reports as type "Crystal Reports" and then ask the user to click a button (in Access) naming who is logged into Windows before creating the reports. I've never had any problems finding the csv files.
Cheers,
Micahel |
|
|
|
|
|
| You are not authorized to post a reply. |
|
|
|
ActiveForums 3.6
|