A DILEMMA FOR ANY ACCESS/EXCEL EXPERT!!"HELP" 4

[DIVINEDAR0956]

Dear Heros,

I have the following procedure to open an excel file and it is suppose to update the links in access:

Private Function OpenEditExcelFile()

Dim XLApp As Object
Dim xlWB As Object
Set XLApp = CreateObject("Excel.Application&quot
XLApp.AskToUpdateLinks = False
XLApp.DisplayAlerts = False

Set xlWB = XLApp.Workbooks.Open("C:\COST\P0033.xls", False)
xlWB.Sheets("Sheet1&quot.Select
XLApp.Visible = True


xlWB.Saved = True
XLApp.AskToUpdateLinks = True
XLApp.DisplayAlerts = True
Set XLApp = Nothing
End Function

I have three questions.

1. I can tell that when the excel file is opened from access it seems to want to update the links. Why is it that in my linked tables the error #Num! appears in almost every column?

2. In this line of the procedure (Set xlWB = XLApp.Workbooks.Open("C:\COST\P0033.xls", False) I would like for the open file name to be generic. In other words I would like to have it open the directory C:\Cost and pick an excel file to open automatically link the worksheet within the file to the proper table within access.

3. How does "AskToUpdateLinks" actually work when opening a file with links.

Any help at all would be appreciated. Thank you. Darlene Sippio
dsippio@comtechsystems.com

 

[scking]

The answer to your first question is that Excel and Access do not always agree on a format but Access INSISTS that if you say it is one type then it is that type. A common problem is that Excel determines the format for you. For instance if Access is expecting a text row and it is something like this in Excel;

X275413
B789061
12B7957
78965

Excel will format cell 1 as text, cell 2 as text, cell 3 as ?, and cell 4 as numeric. I have written a number of Excel macros to help; FormatToNumeric, FormatToText, IsSheetConsistent, etc. Merely ensure the columns are properly formatted before linking to Access as a table.

I have used the following to do very much as you request in question 2. Use this as an example if you wish.

Public Function LinkAll(Optional strFolderSpec As String)

Dim SsType As String
Dim DbHdr As String
Dim sFilename As String
Dim sShortFilename As String
Dim sTablename As String
Dim stblName As String
Dim sExt As String
Dim sMsg As String
Dim intFileNbr As Integer
Dim intPos As Integer
Dim fso As FileSystemObject
Dim oFolder As Folder
Dim oFiles As Files
Dim oFile As File

Set fso = New FileSystemObject
sMsg = "Please enter a valid folder."

VerifyFolderSpec:

If Len(strFolderSpec) = 0 Then
strFolderSpec = GetRootDir & "\INBOX"
End If

If Not DirExists(strFolderSpec) Then
strFolderSpec = InputBox(sMsg, "Folder Information", GetRootDir & "\INBOX&quot
If Len(strFolderSpec) = 0 Then
Exit Function
Else
sMsg = "Folder not found. Please enter a valid folder" & vbCrLf _
& "or 'Cancel' this operation."
GoTo VerifyFolderSpec
End If
End If
Set oFolder = fso.GetFolder(strFolderSpec)
Set oFiles = oFolder.Files

If MsgBox("This will link all Excel files in directory" & vbCrLf _
& strFolderSpec & "." & vbCrLf & vbCrLf _
& "Do you want to Proceed?", vbYesNo, "Proceed with Action?&quot = vbNo Then
Exit Function
End If

For Each oFile In oFiles
intFileNbr = intFileNbr + 1
sFilename = oFile.Path
sShortFilename = oFile.Name
intPos = RevInStr(sFilename, ".&quot
sExt = UCase(Mid$(sFilename, intPos + 1))
intPos = RevInStr(sShortFilename, ".&quot
If intPos = 0 Then GoTo NextFile
sShortFilename = UCase(Mid$(sShortFilename, 1, intPos - 1))
'sTablename = "ANALYST-IN-DATA" & intFileNbr
sTablename = sShortFilename
If sExt = "XLS" Then
'DoCmd.TransferSpreadsheet TransferType:=acLink, SpreadsheetType:=acSpreadsheetTypeExcel97, TableName:=sTablename, Filename:=sFilename, HasFieldNames:=True
If InStr(1, sShortFilename, "ANALYST-IN-DATA&quot > 0 Then
DoCmd.TransferSpreadsheet TransferType:=acLink, _
SpreadsheetType:=acSpreadsheetTypeExcel97, TableName:=sShortFilename, Filename:=sFilename, HasFieldNames:=True
ElseIf InStr(1, sShortFilename, "CROSS-REFERENCE&quot > 0 Then
DoCmd.TransferSpreadsheet TransferType:=acLink, _
SpreadsheetType:=acSpreadsheetTypeExcel97, TableName:="QML-IN-MASTER", Filename:=sFilename, HasFieldNames:=True
ElseIf InStr(1, sShortFilename, "TPP-IN&quot > 0 Then
DoCmd.TransferSpreadsheet TransferType:=acLink, _
SpreadsheetType:=acSpreadsheetTypeExcel97, TableName:="TPP-IN-DATA", Filename:=sFilename, HasFieldNames:=True
End If
End If
NextFile:
DoEvents
Next oFile
'GetFileList = s

'sFilename = "C:\Apps\ToMgmt2000\Outdata\ANALYST-OUT-DATA (5N1-4-23-14).xls"
'DoCmd.TransferSpreadsheet TransferType:=acLink, SpreadsheetType:=acSpreadsheetTypeExcel97, TableName:="ANALYST-IN-DATA-TEST", Filename:=sFilename, HasFieldNames:=True

End Function

Question 3 I'm not sure what you want. It should prompt the user with a question about whether to update the links.








-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[DIVINEDAR0956]

Scking,

Thank you for your quick response. Please help me through this. I understand what your procedure will do but I am a beginner and somewhat okay but I get an error message here:


Dim fso As FileSystemObject
Dim oFolder As Folder
Dim oFiles As Files
Dim oFile As File

Set fso = New FileSystemObject


The error is object not defined. I'm not sure how this works but when you type As you get a list of controls that can be used and FileSystemObject is not one of them. Neither is Folder, Files, or File . So is there something else special that you do to make them work. I tried changing FileSystemObject to Object but then I get an error on the Set fso .

As I mentioned I understand your procedure very well and it reads as if it will do exactly what I want it to do if you can help me get up and running.

Thank you for your help.
Darlene Sippio
dsippio@comtechsystems.com

 

[MichaelRed]

Missing reference to the scripting object. Look under:

Tools --> References

"Tools" is a menu bar item in Module Design Mode.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[DIVINEDAR0956]

Thank you.

What is the name of the reference or similar reference I should refer to. Darlene Sippio
dsippio@comtechsystems.com

 

[scking]

Microsoft Scripting Runtime reference which is the scrrun.dll library will allow you to run the code.


-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[DIVINEDAR0956]

Thank you. I found it and it runs well. Now I get an error message here:

DirExists(strFolderSpec)

The error I get is Sub or Function not defined. What do I do? I don't understand this one.

Thank you. Darlene Sippio
dsippio@comtechsystems.com

 

[scking]

I provided you with a piece of my application. DirExists is simply a wrapper around FileSystemObject that uses FolderExists. The name should provide the clue, then it would be relatively easy to use either FileSystemObject or the VBA intrinsic command DIR. Good Luck.

Public Function DirExists(DIREC As String) As Boolean

Dim strDir As String
Dim fso As FileSystemObject

Set fso = New FileSystemObject
strDir = DIREC

If fso.FolderExists(strDir) Then
DirExists = True
Else
DirExists = False
End If
Set fso = Nothing

End Function
-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[DIVINEDAR0956]

Thank you that worked. As I stated I'm new at this but catch on real good. I appreciate your help. If I get on your nerves please let me know but you have done so good thus far.

Now I have an error message here:

intPos = RevInStr(sFilename, ".&quot

Could you also tell me what does this line do. I get the same error message Sub or Function not defined. Also do I give sFilename an actual file name or is this the file I pick from the directory that will automatically link to access.

Is there a reference guide or any guide that will explain to me what such strings mean and do, such as RevInStr. It would help me a lot if I could understand what such strings do.

Thank you.
Darlene Sippio
dsippio@comtechsystems.com

 

[scking]

What version of Access are you using. There is an intrinsic function RevInStr which returns the position of a string within a string that is available, I believe, in Access 2002. Here's the deal. I created my own RevInStr to mimic exactly what would work in later versions.

Have you modified LinkAll yet? It was built for a special purpose and you would need to modify it for your app.

Public Function RevInStr(StringToSearch As String, _
SearchString As String)

Dim lngLenSearchString As Long
Dim lngLenStringToSearch As Long
Dim lngSearchBack As Long
Dim i As Integer
Dim strWindow As String
Dim strStringToSearch As String
Dim strSearchString As String

lngLenSearchString = Len(SearchString)
lngLenStringToSearch = Len(StringToSearch)
lngSearchBack = lngLenStringToSearch - lngLenSearchString + 1
strStringToSearch = UCase(StringToSearch)
strSearchString = UCase(SearchString)

For i = lngSearchBack To 1 Step -1
strWindow = Mid$(strStringToSearch, i, lngLenSearchString)
If strWindow = strSearchString Then
'RevInStr = i
GoTo Exit_Search
End If
Next i

Exit_Search:
RevInStr = i

End Function
-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[DIVINEDAR0956]

I was editing as I ran it then I figured I would know what to edit. I haven't changed it to my file names or table names yet so far. Now I think I'm going to get into changing information to me my criteria because I've ran into this error:

Set fso = New FileSystemObject I get the error that object required. Do I set FileSystemObject to my directory that will store all my excel files or set it as an object.

If not then why would I be getting an error message?

Thank you. Darlene Sippio
dsippio@comtechsystems.com

 

[scking]

It's very difficult to tell from the little information provided. I suspect that there is no 'Dim fso' in the routine.

-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[DIVINEDAR0956]

scking,

Thank you for all your help and I figured it out, I left off the word New and now it works but I have to figure out the rest. Thank you for your help and you might be hearing from me before the day is over. The HEAVEN will be your STARS on this one.

Thank you again. Darlene Sippio
dsippio@comtechsystems.com

 

[scking]

1) Check Reference
2) Check Dim statement for fso

The following would be required to work with files using the FileSystemObject.

Dim fso As FileSystemObject
Dim oFolder As Folder
Dim oFiles As Files
Dim oFile As File

Set fso = New FileSystemObject
Set oFolder = fso.GetFolder(strFolderSpec)
Set oFiles = oFolder.Files
For Each oFile In oFiles
' Your file handling here
DoEvents
Next oFile

-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------