Excel Import to Access Prevent "file now available" message.

[Speiro1]

Hi All

I pulled the below code from a post to import excel files into access. I do not like using code I don't understand but in this case I had no choice, I am not that proficient in VBA and it works. The only problem I appear to have is that after I import files I get a message that states "file now available," does anyone know how to suppress this? Any help would be greatly appreciated. I did some surfing of the boards but found that everyone that has this problem has different code so I cannot figure out how to apply their fixes to my code.


Option Compare Database

Option Explicit

Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Private Sub Command0_Click()
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String
Dim WrksheetName As String
Dim i As Integer
Dim oApp As Object

OpenFile.lStructSize = Len(OpenFile)
OpenFile.hwndOwner = Form.Hwnd
'OpenFile.hInstance = App.hInstance
sFilter = "acSpreadsheetTypeExcel8 (*.xlsx)" & Chr(0) & "*.xlsx" & Chr(0)
OpenFile.lpstrFilter = sFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = "C:\Windows\DeskTop"
OpenFile.lpstrTitle = "Use the Comdlg API not the OCX"
OpenFile.flags = 0
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
MsgBox "The User pressed the Cancel Button"
Else
MsgBox "The user Chose " & Trim(OpenFile.lpstrFile)
End If
Set oApp = CreateObject("Excel.Application")
oApp.Workbooks.Open OpenFile.lpstrFile
With oApp
'the field names of teh import sheet and the table they will be imported too must match or you will get the following Run-time error '2391
' this automatically creats the range of the columns.
DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel8, "Table1", OpenFile.lpstrFile, True
End With

Set oApp = Nothing
End Sub

 

[kjv1611]

Without digging into the code, my guess is that it's trying to import a file that is not there in the directory given.

Have you tried stepping through the code? Open the VB Editor, move your cursor within that procedure, and begin pressing the <F8> key on your keyboard. As you progress through the code, it'll highlight the current line. When it reaches your error, you can then see what line is causing the problem.

I may try to dig through the code a little more later. Also, it could be related to file locking. I just glanced at the code again, and see mentions of getting the Window handle name. It's really not best to be working on importing a "live" or open file if possible. Matter of fact, for instance, if you link data from Access to Excel, and keep the Excel file open, then re-open the Access database after the link is set to Excel, then Access will not allow you to make changes to the database until the spreadsheet is closed. I realize that's in the opposite direction, but regardless of program or direction, the applications need a static file to import/link from, generally speaking.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Speiro1]

I have the following code in a command button which is used to import excel files into my access db. When the browsing window pops up if I hit cancel I get the following error. Run-Time error '1004': " could not be found. check the spelling of the file name. Any help you can provide would be greatly appreciated.


Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type
Private Sub Command26_Click()
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String
Dim WrksheetName As String
Dim i As Integer
Dim oApp As Object
OpenFile.lStructSize = Len(OpenFile)
OpenFile.hwndOwner = Form.Hwnd
'OpenFile.hInstance = App.hInstance
sFilter = "acSpreadsheetTypeExcel8 (*.xlsx)" & Chr(0) & "*.xlsx" & Chr(0)
OpenFile.lpstrFilter = sFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = "C:\Windows\DeskTop"
OpenFile.lpstrTitle = "Use the Comdlg API not the OCX"
OpenFile.flags = 0
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
MsgBox "You pressed the Cancel Button"
Else
MsgBox "You Chose " & Trim(OpenFile.lpstrFile)
End If
Set oApp = CreateObject("Excel.Application")
oApp.Workbooks.Open OpenFile.lpstrFile
With oApp
'the field names of teh import sheet and the table they will be imported too must match or you will get the following Run-time error '2391
' this automatically creats the range of the columns.
DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel8, "Management", OpenFile.lpstrFile, True
End With

Set oApp = Nothing
End Sub

 

[kjv1611]

Without delving into the code, here's what's happening:
1. You click the button
2. The file browser window opens, but you hit Cancel instead of selecting a file.
3. The code continues, trying to work with a file by the name of "" or Null String. That is because, it's still going to return something to the main procedure for the file name, even though you didn't select one.

As far as how to have it not throw error messages for this, you'll need to setup conditions or else error handling. This one should be simple enough to handle with a condition.

Could be as simple as :
Before you even get to the part where it's SUPPOSE to be checking for no file selected, you could instead use this:

If OpenFile.lpstrFileTitle = vbNullString Then Goto ExitSub

Then, you need to have an ExitSub: section before the error handler - if you intend to have an error handler - and put any final closing code you want to happen, even if the action skips all other code. For instance, you instantiated an object or two, so you may need to clear those objects out - put those in the ExitSub portion of the code, and make sure you put the Exit Sub at the end, just before your error handler.

But for the main part, try using what I put in the code box.

Also, for posting questions here, it would help a LOT if you do 2 things with code:
[ol 1]
[li]Put it in a code box, like I did. Easy way to do that now is select the block of text that is code, and lick the code button above where you're entering your text.[/li]
[li]Highlight the actual line that gives you the error you receive - don't leave it to us to "figure it out." If we don't have time, and it takes too long, you may never get an answer.[/li]
[/ol]

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57