I am tring to import an Excel file directly to a table in Access 2003. The code runs but it locks up the app and I need to do a ctrl/alt/del to get out. Here is the code:
Private Declare Function GetOpenFileName Lib "comdlg32.d ll" Alias _
"GetOpenFileNam eA" (pOpenfilename As OPENFILENAME) As Long
Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilt er 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 ImportFile_Clic k()
On Error GoTo Err_ImportFile_ 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.lStruc tSize = Len(OpenFile)
OpenFile.hwndOw ner = Form.Hwnd
'OpenFile.hInst ance = App.hInstance
sFilter = "acSpreadsheetT ypeExcel9 (*.xls)" & Chr(0) & "*.xls" & Chr(0)
OpenFile.lpstrF ilter = sFilter
OpenFile.nFilte rIndex = 1
OpenFile.lpstrF ile = String(257, 0)
OpenFile.nMaxFi le = Len(OpenFile.lp strFile) - 1
OpenFile.lpstrF ileTitle = OpenFile.lpstrF ile
OpenFile.nMaxFi leTitle = OpenFile.nMaxFi le
OpenFile.lpstrI nitialDir = "C:\"
OpenFile.lpstrT itle = "Locate and Select the File for Import"
OpenFile.flags = 0
lReturn = GetOpenFileName (OpenFile)
Set oApp = CreateObject("E xcel.Applicatio n")
oApp.Visible = True
oApp.Workbooks. Open OpenFile.lpstrF ile
With oApp
.Visible = True
With .Workbooks(.Wor kbooks.Count)
For i = 1 To .Worksheets.Cou nt
WrksheetName = .Worksheets(i). Name
DoCmd.TransferS preadsheet acImport, cSpreadsheetTyp eExcel9, _
"AIS Release and Transport Status", OpenFile.lpstrF ile, True
Next i
End With
End With
Set oApp = Nothing
Screen.Previous Control.SetFocu s
DoCmd.FindNext
Exit_ImportFile _Click:
Exit Sub
Err_ImportFile_ Click:
MsgBox Err.Description
Resume Exit_ImportFile _Click
End Sub
I'am stuck as to what to try next. If anyone has an idea I'd sure like to here it. Thanks in advance.
3  4743  
I am tring to import an Excel file directly to a table in Access 2003. The code runs but it locks up the app and I need to do a ctrl/alt/del to get out. Here is the code:
Private Declare Function GetOpenFileName Lib "comdlg32.d ll" Alias _
"GetOpenFileNam eA" (pOpenfilename As OPENFILENAME) As Long
Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilt er 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 ImportFile_Clic k()
On Error GoTo Err_ImportFile_ 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.lStruc tSize = Len(OpenFile)
OpenFile.hwndOw ner = Form.Hwnd
'OpenFile.hInst ance = App.hInstance
sFilter = "acSpreadsheetT ypeExcel9 (*.xls)" & Chr(0) & "*.xls" & Chr(0)
OpenFile.lpstrF ilter = sFilter
OpenFile.nFilte rIndex = 1
OpenFile.lpstrF ile = String(257, 0)
OpenFile.nMaxFi le = Len(OpenFile.lp strFile) - 1
OpenFile.lpstrF ileTitle = OpenFile.lpstrF ile
OpenFile.nMaxFi leTitle = OpenFile.nMaxFi le
OpenFile.lpstrI nitialDir = "C:\"
OpenFile.lpstrT itle = "Locate and Select the File for Import"
OpenFile.flags = 0
lReturn = GetOpenFileName (OpenFile)
Set oApp = CreateObject("E xcel.Applicatio n")
oApp.Visible = True
oApp.Workbooks. Open OpenFile.lpstrF ile
With oApp
.Visible = True
With .Workbooks(.Wor kbooks.Count)
For i = 1 To .Worksheets.Cou nt
WrksheetName = .Worksheets(i). Name
DoCmd.TransferS preadsheet acImport, cSpreadsheetTyp eExcel9, _
"AIS Release and Transport Status", OpenFile.lpstrF ile, True
Next i
End With
End With
Set oApp = Nothing
Screen.Previous Control.SetFocu s
DoCmd.FindNext
Exit_ImportFile _Click:
Exit Sub
Err_ImportFile_ Click:
MsgBox Err.Description
Resume Exit_ImportFile _Click
End Sub
I'am stuck as to what to try next. If anyone has an idea I'd sure like to here it. Thanks in advance.
Before trying anything else, take this portion of your code: -
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
-
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
-
and move it to a standard module and make it Public. Let me know if that helped
 ADezii 8,834
Recognized Expert Expert
I am tring to import an Excel file directly to a table in Access 2003. The code runs but it locks up the app and I need to do a ctrl/alt/del to get out. Here is the code:
Private Declare Function GetOpenFileName Lib "comdlg32.d ll" Alias _
"GetOpenFileNam eA" (pOpenfilename As OPENFILENAME) As Long
Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilt er 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 ImportFile_Clic k()
On Error GoTo Err_ImportFile_ 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.lStruc tSize = Len(OpenFile)
OpenFile.hwndOw ner = Form.Hwnd
'OpenFile.hInst ance = App.hInstance
sFilter = "acSpreadsheetT ypeExcel9 (*.xls)" & Chr(0) & "*.xls" & Chr(0)
OpenFile.lpstrF ilter = sFilter
OpenFile.nFilte rIndex = 1
OpenFile.lpstrF ile = String(257, 0)
OpenFile.nMaxFi le = Len(OpenFile.lp strFile) - 1
OpenFile.lpstrF ileTitle = OpenFile.lpstrF ile
OpenFile.nMaxFi leTitle = OpenFile.nMaxFi le
OpenFile.lpstrI nitialDir = "C:\"
OpenFile.lpstrT itle = "Locate and Select the File for Import"
OpenFile.flags = 0
lReturn = GetOpenFileName (OpenFile)
Set oApp = CreateObject("E xcel.Applicatio n")
oApp.Visible = True
oApp.Workbooks. Open OpenFile.lpstrF ile
With oApp
.Visible = True
With .Workbooks(.Wor kbooks.Count)
For i = 1 To .Worksheets.Cou nt
WrksheetName = .Worksheets(i). Name
DoCmd.TransferS preadsheet acImport, cSpreadsheetTyp eExcel9, _
"AIS Release and Transport Status", OpenFile.lpstrF ile, True
Next i
End With
End With
Set oApp = Nothing
Screen.Previous Control.SetFocu s
DoCmd.FindNext
Exit_ImportFile _Click:
Exit Sub
Err_ImportFile_ Click:
MsgBox Err.Description
Resume Exit_ImportFile _Click
End Sub
I'am stuck as to what to try next. If anyone has an idea I'd sure like to here it. Thanks in advance.
There are 3 Major Problems as I see it and puppydogbuddy already cleared up 1 of them. The other 2 are as follows: - Typographical Error in TransferSpreads heet line.
- DoCmd.TransferSpreadsheet acImport, cSpreadsheetTypeExcel9, _
-
"AIS Release and Transport Status", OpenFile.lpstrFile, True
-
---------------------------- SHOULD READ ----------------------------
-
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
-
"AIS Release and Transport Status", OpenFile.lpstrFile, True
- The code is correctly looping through the Worksheets but the same Worksheet will be imported each time with this Method. Modify the Loop as such, and it should work correctly.
-
With oApp
-
.Visible = True
-
With .Workbooks(.Workbooks.Count)
-
For i = 1 To .Worksheets.Count
-
WrksheetName = .Worksheets(i).Name
-
.Worksheets(i).Activate
-
'The next 3 lines will obtain the last data cell reference for each Worksheet
-
strLastDataColumn = Chr(Selection.SpecialCells(xlLastCell).Column + 64)
-
strLastDataRow = Selection.SpecialCells(xlLastCell).Row
-
strLastDataCell = strLastDataColumn & strLastDataRow 'e.g. J123
-
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
-
"AIS Release and Transport Status", OpenFile.lpstrFile, True, .Worksheets(i).Name & "!A1:" & strLastDataCell
-
Next i
-
End With
-
End With
-
Thanks for the response guys, I got it working now. This site is great for us newbies to Access.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Vikram |
last post by:
Hi,
Given below is my task.
An user can open the xls file from my website (loaded on the top frame).
After filling the Excel, he can click a send button at the bottom frame. By
clicking the button i have to save the file in a shared directory on the web
server by a unique name.
Is it possible ?
|
by: Fred |
last post by:
Hi.
How do I import while mapping an excel table to an access table please???
I've searched around and all I can find is a software product or code that
does the same thing as the access wizard.....
If I have to write vb code, where is a sample???
Or do I import the excel in to a new spreadsheet and then
write some kind of querey to move the data from that table
|
by: G |
last post by:
Hi, I'm trying to import an Excel spreadsheet into Access 2000. I
select File>GetExternal Data>Import, but when I get to the dialog box
that asks me to select the file I want to import from, the pull-down
"files of type" list only lets me choose from Access file types.
It doesn't contain any Excel file types or any other file type at
all. If I type in my excel file manually it says "The ADP file is not
in the correct format for a...
|
by: deko |
last post by:
I've been trying to use the Access Import Wizard to expedite importing data
into my mdb. The nice thing about the wizard is that I can import from
different file formats - txt, xls, even Outlook - and dump everything into a
table.
The problem is once I have the data imported into a new table, I can't do
much with it. If I try to run an Append query and insert data from the new
table into an existing table, the query fails - "Error...
|
by: autoEx |
last post by:
I am using #import directive to import the excel library to make excel files but the excel object is not recognized by my application. Here is what I do: I have simple dialog based MFC application in VC 6.00 In the dialog .cpp file, I add the following
#import "D:\\Program Files\\Microsoft Office\\Office\\MSO9.DLL" no_namespac
#import "C:\\Program Files\\Common Files\\Microsoft Shared\\VBA\\VBA6\\VBE6EXT.OLB" no_namespac
#import...
| | |
by: Child of His |
last post by:
I have been through every trick I know, or has been suggested.
I have a one to two million line fixed field database in text format.
I want to bring it into Access 97.
When I use the external data import, the list of files to import does
not include text files of any type, only other database formats.
On a 102,000 line text file, I was able to split it with Word 97,
import the split files into Excel 97 one at a time, and then save...
|
by: baling |
last post by:
Hi....
Hi everybody, i have a code that i make in VBA and know I want to use this code in to VB6. But i don't know how to use that code in to VB 6.0
Please correct this code so i can use it in VB 6.0.
Code:
--- use in mainform -------
Option Compare Database
Option Explicit
|
by: PW |
last post by:
Convert them to CSV in Excel, then use TransferText (which does not
work correctly and also doesn't accept XLS/Excel files directly) or
create a link to an Excel XLS workbook and do an Append Query (as some
have suggested here)??
I find that hard to believe as Access is part of MS Office and Visual
FoxPro is not. I split my time using both and I can do a simple copy
command to XLS in VFP or an Append command to DBF or use Office...
|
by: provor |
last post by:
Hello,
I have the following code that I am using when a user presses a button to import an excel file into a table. The code is hard coded to point to the correct table. This works great for this one table. My problem is I have two buttons I want to use this code for for the two buttons would put the data in different tables. I have tried copying and changing a few things and nothing will work for me. The code is set up in a module and then I...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
