Forgot password? | Forgot username? | Register

Network problems with vba report [solved]

Network problems with vba report [solved]

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

Administrator has disabled public posting. Please login or register in order to proceed.

Re: Network problems with vba report [solved]

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

Administrator has disabled public posting. Please login or register in order to proceed.

Re: Network problems with vba report [solved]

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 … older.aspx

Cheers

JP

Administrator has disabled public posting. Please login or register in order to proceed.

Re: Network problems with vba report [solved]

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

Administrator has disabled public posting. Please login or register in order to proceed.

Re: Network problems with vba report [solved]

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

Administrator has disabled public posting. Please login or register in order to proceed.

Re: Network problems with vba report [solved]

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

Bernard Marshall (Axiell Melbourne)
useravatar
Offline
43 Posts
Administrator has disabled public posting. Please login or register in order to proceed.

Re: Network problems with vba report [solved]

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

Administrator has disabled public posting. Please login or register in order to proceed.

Re: Network problems with vba report [solved]

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

Administrator has disabled public posting. Please login or register in order to proceed.

Re: Network problems with vba report [solved]

Hi,

I thought I should flag up that I still have not resolved the above issue.  When the  DBQ is not specified in the VB code, and the report is run from another login / user - the report fails with the same error message. The query definition still 'remembers' the path to the computer that created the report, not the current user.

When I create simple excel reports that only use one worksheet/query table this problem does not happen.

My only guess as to the solution might be that I need to specify the entire connection and sql string for each query table rather than relying on those embedded in the worksheet.

Crystal reports is not an option due to specifications of the designers, and the report does look rather nice when it works, however at the moment I am the only one that can run the report. Perhaps this is not a bad thing.


I hope I am wrong?

Administrator has disabled public posting. Please login or register in order to proceed.

Re: Network problems with vba report [solved]

ok, back for more on this. ...

The problem occurs when more than one query tables are specified and joined using MS query, from within Excel.  As it is only possible to save one report file, the Excel report, to the EMu server, the link to the .dqy query file is lost once the report is run from another pc, but works from the pc of the report designer.

So the solution might be to specify the table joins using Visual Basic entirely and not MS Query, which by the looks of things will be  tricky.
Alternativelty each table could be imported seperately into the excel file and the report generated using a sequence of Vlookups, which I dont trust much.

Administrator has disabled public posting. Please login or register in order to proceed.

Re: Network problems with vba report [solved]

Problem Solved, and it was a simple solution in the end,thanks Bern! -  please accept my apologies for the reams of useless code posted earler.  All that was needed for the reports to successfully retrieve data from the .csv files was two lines of code in the format.....

Code:


Worksheets("AllData").Activate
Range("b2").QueryTable.Connection = "ODBC;DSN=EMu Catalogue;"
Range("b2").QueryTable.Refresh

all problems are now fixed and the report runs fine, with multiple tables connected via MS Query. I think the issue may have been that when the connection code was wrong, the program simply ignored it and looked for the original query file on my PC, which then only worked when I ran the report and failed with other users.

Administrator has disabled public posting. Please login or register in order to proceed.

Re: Network problems with vba report [solved]

Ok Back for more on this one I am afraid.. and all was going so well! 

So... for the most part my excel reports are running fine and multiple users can run them with no problems. However recently another user develped a report and when I downloaded the report and refreshed the query I got the attached error messagehttps://emu.axiell.com/images/agorapro/attachments/6327/23072011-0021_MS-Query-error.jpeg

This is an Excel/MS Query problem rather than something to do with EMu, but I need to sort it in order for different users to run the reports. No VBA code is involved this time, just the background query running from within the Excel file. Somewhere along the line the good old users path gets encoded into the file rather than a dynamic multi user bit of code, and it causes the query to fail to refresh when a different users runs the report. Many of my reports dont have this issue, so I know there is a fix but I cant work it out, I have a suspision that it can be resolved by a particular setting in the query properties settings.. but I have not resolved this yet.

Administrator has disabled public posting. Please login or register in order to proceed.

Re: Network problems with vba report [solved]

Just found out what the problem was - I had included a pivot chart in the report based on the external .csv files. When I removed the pivot chart from the report there was no error message. what I think happened was that the pivot chart was storing the erroneous path somewhere and in contrast to query tables, excel does not let you change the SQL behind the pivot chart. I overcame this by creating a query table to import the data into the report and then created a new pivot chart based on the imported data.

I have found that when using MS query to create an SQL string sometimes and without warning Excel/ M Query may insert the entire path into the .csv file including the user's path (C:\Documents and Settings\[Your username]\Local Settings\....)  into the SQL.  It is possible to manually delete this by checking the SQL and deleting the path so just the table name is left.

Administrator has disabled public posting. Please login or register in order to proceed.
There are 0 guests and 0 other users also viewing this topic

Board Info

Board Stats
 
Total Topics:
601
Total Polls:
0
Total Posts:
1362
Posts today:
2
User Info
 
Total Users:
816
Newest User:
Gregory Brown
Members Online:
3
Guests Online:
220