Need to grab Path and Filename to run Import txt file process...

[ThriftyFinanceGirl]

I may not have explained this right the first time, but I need to import
several txt files each month and allow the user to pick the file.

So what I need to do is have a way to grab the path and filename from the
common dialog (I think this would be the best way). My current transfertxt
file code works, but the path is hardcoded. Current code below:

DoCmd.TransferText acImportDelim, "R5561115ImportSpecs",
"R5561115Import", strInputFileName, False, ""


SECOND question: I used the wizard to help me SAVE the file importspecs.
When I move this app live, where are those specs saved so that I can continue
to use that reference? I can't seem to find that information anywhere on the
MS site. They say to use the wizard to save the import specifications (which
I did) however, I don't know where in the heck it is saved!

 

[Dirk Goldgar]

I may not have explained this right the first time, but I need to import
several txt files each month and allow the user to pick the file.

So what I need to do is have a way to grab the path and filename from the
common dialog (I think this would be the best way). My current
transfertxt
file code works, but the path is hardcoded. Current code below:

DoCmd.TransferText acImportDelim, "R5561115ImportSpecs",
"R5561115Import", strInputFileName, False, ""

I use the code to call the Windows File Open/Save API that is posted here:

http://www.mvps.org/access/api/api0001.htm

SECOND question: I used the wizard to help me SAVE the file importspecs.
When I move this app live, where are those specs saved so that I can
continue
to use that reference? I can't seem to find that information anywhere on
the
MS site. They say to use the wizard to save the import specifications
(which
I did) however, I don't know where in the heck it is saved!

The import/export specifications are saved in the database file itself, so
they will move with the database.

 

[Alex Dybenko]

Hi,
open file dialog code: http://www.mvps.org/access/api/api0001.htm

import specifications are stored in MSysIMEXSpecs and MSysIMEXColumns system
tables, you can set option to view system tables in options dialog


--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[ThriftyFinanceGirl]

Dirk, thanks so much, but in the previous post where I put this, I noted that
I copied this information that you pointed me toward.

The very first declaration gets stuck with "user-defined type not defined"
(code below

Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean

Any help with that?

 

[ThriftyFinanceGirl]

You guys are being great, but I can't get that code to run. See my previous
response... could you give me a suggestion as to where to go from here?

 

[Jack Leach]

Are you sure you've copy&pasted all of the code from the link? The first
non-commented code should be the type tagOPENFILENAME that you are hanging up
on...

Type tagOPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
strFilter As String
strCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
strFile As String
nMaxFile As Long
strFileTitle As String
nMaxFileTitle As Long
strInitialDir As String
strTitle As String
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
strDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type



If you've copy and pasted the module in full as shown in the link you should
not be having any problems with it.


hth


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Jack Leach]

Also, this module code needs to go in a standard module, not a class (form)
module.

Then, from any other module, use the following line to call it:

strFilename = GetOpenFile


--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[ThriftyFinanceGirl]

I got it after I wrote that, and the testit function works, now, am I placing
the correct object in my code? "strInputFileName"?

 

[Jack Leach]

if strInputFileName is a string variable whose value is defined by the
GetOpenFile function provided, then yes, you would be placing the code
correctly in the output command. Be sure to utilize the filter to keep your
users from accidently selecting an incompatible filetype.

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[ThriftyFinanceGirl]

Jack is my Hero!

if strInputFileName is a string variable whose value is defined by the
GetOpenFile function provided, then yes, you would be placing the code
correctly in the output command. Be sure to utilize the filter to keep your
users from accidently selecting an incompatible filetype.

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[ThriftyFinanceGirl]

Alex,

Thanks so much, now, I'm not sure I understand this part of your comment:

"import specifications are stored in MSysIMEXSpecs and MSysIMEXColumns system
tables, you can set option to view system tables in options dialog "

Could you explain? Or, do you know how to, in code, set all the import
specs? I wouldn't mind having them in the code, just didn't know how to set
them all and the wizard saving it for me was an easy way out.

 

[Dirk Goldgar]

Jack is my Hero!

<pout>

 

[ThriftyFinanceGirl]

But Dirk was the Knight on the White Horse who started it all!

 

[Jack Leach]

Sorry for jumping in there Dirk. In this case, I think Ken Getz has us both
beat, credited or not.



--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Dirk Goldgar]

But Dirk was the Knight on the White Horse who started it all!

I feel better now.

 

[Dirk Goldgar]

Sorry for jumping in there Dirk. In this case, I think Ken Getz has us
both
beat, credited or not.

No doubt about it.

 

[Dirk Goldgar]

Dirk, thanks so much, but in the previous post where I put this, I noted
that
I copied this information that you pointed me toward.

Note, by the way, that people responding to one of your posts generally have
no idea what you may have posted in other threads. So if you're going to
repost a question, you have to state all the incidentals over again.

 

[Alex Dybenko]

Hi,
there just like normal tables, so you can add/delete/edit data there. Only
thing - you need to do this using select query, if you open tables
directly - they are read-only

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[BTU_needs_assistance_43]

I've got a handy piece of code here I use in Excel. I have it set up so that
it searches for files in a specific folder but can be made more or less
specific as needed. It also narrows the search by file type and date
modified, though those can be removed or specified as well. This code will
find the file names and output them into a list of cells in Excel. You can
pretty easily modify it as you need.


Private Sub FileSearch_Click()

With Application.FileSearch
.NewSearch
.LookIn = "C:\Folder\Another Folder\Subfolder"
.SearchSubFolders = True
.LastModified = msoLastModifiedYesterday (Narrows search to recent files)
.FileType = msoFileTypeExcelWorkbooks (Narrows search by type of files)
If .Execute > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" Excel file(s) found."
Worksheets("sheet1").Activate
Worksheets("sheet1").Range("A2").Select
For i = 1 To .FoundFiles.Count
ActiveCell.Value = .FoundFiles(i)
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
Next i
Else
MsgBox "There were no Excel files found."
End If
End With

End Sub

 

[BTU_needs_assistance_43]

I forgot to mention that when it outputs in a cell it provides the entire
path and file name of the files you searched for.