Set file path to a variable

[boblarson]

Yep, it ALL goes in as it is used throughout the stuff.

Or you could go with the other option I posted later (which had a quote from the Access VBA Help File) about using the built in FileDialog.

 

[benkingery]

You mentioned that you prefer the API...hopefully not because its easier...

Can you tell me about the built in Dialog? This really should be pretty simple I thought.

 

[boblarson]

You mentioned that you prefer the API...hopefully not because its easier...

Can you tell me about the built in Dialog? This really should be pretty simple I thought.

go into your VBA window and type FileDialog in the Immediate window and then put your cursor in that word and hit F1 to bring up the help on it. The help should explain it very well. Also you have the quote I quoted from the help file, which is basically the same thing you'll get if you open your help file.

That is, if you have Access 2003 or above. (maybe 2002, not sure about that).

If you have a previous version (2000, 97, etc) then you will need the API.

 

[benkingery]

Looks like this is the syntax:

Dim dlgSaveAs As FileDialog  Set dlgSaveAs = Application.[B]FileDialog[/B]( _     FileDialogType:=msoFileDialogSaveAs)  dlgSaveAs.Show

So, if I want to put the file as the variable in the TransferText command, what part of the above code do I use as that variable? Does what I asked make sense?

Thanks for all your help by the way. You've been a great help.

 

[boblarson]

Instead of SAVE AS use OPEN:

  Dim dlgOpen As FileDialog
  Set dlgOpen = Application.FileDialog(msoFileDialogOpen)
  dlgOpen.Show
  Debug.Print dlgOpen.SelectedItems(1)

And then you would use for your transfer:

  Dim dlgOpen As FileDialog
  Set dlgOpen = Application.FileDialog(msoFileDialogOpen)
  dlgOpen.Show
  DoCmd.TransferText acImportDelim, "specNameHere", "TableNameHere", dlgSaveAs.SelectedItems(1)

 

[benkingery]

Awesome! Thanks.

So, both pieces of code I need to use, right? Put each one into its own module? Or into the same module? Is one function calling the results of the second?

Thanks again

 

[boblarson]

Awesome! Thanks.

So, both pieces of code I need to use, right? Put each one into its own module? Or into the same module? Is one function calling the results of the second?

Thanks again

Nope, with this one you use just the very last one I used and only in the place you are importing the text. It doesn't need to go into a standard module or anything like that.

 

[benkingery]

Hmmmm. I'm now getting "Compile error: User-defined type not defined"

It highlights this section of code:

dlgOpen As FileDialog

 

[boblarson]

Hmmmm. I'm now getting "Compile error: User-defined type not defined"

It highlights this section of code:

dlgOpen As FileDialog

Remember you have to set a reference to

Microsoft Office X.0 Objects

(X being 10 for 2002, 11 for 2003, and 12 for 2007)

 

[benkingery]

Remember you have to set a reference to

Microsoft Office X.0 Objects

(X being 10 for 2002, 11 for 2003, and 12 for 2007)

Wow! I'm turning out to be a real dweeb here. I'm not sure how I do that.

I am using access 2003, but my DB is in Access 2000 file format

 

[boblarson]

Wow! I'm turning out to be a real dweeb here. I'm not sure how I do that.

I am using access 2003, but my DB is in Access 2000 file format

It still would need to be the 2003 reference.

1. Go to the VBA window

2. Go to TOOLS > REFERENCES

and then scroll down the list until you find

Microsoft Office 11.0 Objects

and check the checkbox next to it.

close the references dialog and that should do it for you.

 

[benkingery]

Within my references, I have "Microsoft Access 11.0 Object Library" checked. Is this correct?

 

[boblarson]

Within my references, I have "Microsoft Access 11.0 Object Library" checked. Is this correct?

No, you ALSO want Microsoft OFFICE 11.0 Object Library checked.

 

[benkingery]

No, you ALSO want Microsoft OFFICE 11.0 Object Library checked.

All I have available is 12.0. Will that work?

 

[boblarson]

All I have available is 12.0. Will that work?

It WILL work for you, but you might run into problems if others do not have any Office 2007 apps on their machine.

 

[benkingery]

Got it. I'll make sure all users have at least 11.0 library in their references.

Sorry to bug again. YOu are just such a wealth of knowledge....

Can I default the open to a particular directory?

 

[boblarson]

Got it. I'll make sure all users have at least 11.0 library in their references.

Sorry to bug again. YOu are just such a wealth of knowledge....

Can I default the open to a particular directory?

Put this (with the applicable directory) before the code which shows the dialog (the fldlg.Show):

dlgSaveAs.InitialFileName = "C:\Temp\"

 

[benkingery]

Just kidding. I found that easy enough.

I have this code and now I'm getting the following error: "Run-time error '424': Object Required"

Public Function CSNImp()
Dim dlgOpen As FileDialog
  Set dlgOpen = Application.FileDialog(msoFileDialogOpen)
  dlgOpen.InitialFileName = "Y:\Access Databases\Import Files to CRM DB\CSN Stores"
  dlgOpen.Show
  DoCmd.TransferText acImportDelim, "CSSalesTransactions", "ORDERS_CSN_OrderBuffer", dlgSaveAs.SelectedItems(1)
End Function

 

[boblarson]

You still have
dlgSaveAs.SelectedItems(1)

instead of

dlgOpen.SelectedItems(1)

in the transfer Text line.

 

[benkingery]

Got it. I'm officially lame.

Thanks again. Have a great weekend. I'm out of here.