Create an import Specification from an excel 2

[MICKI0220]

I need to create an import specification on an excel file that a user can type in the month and year..

Here is the situation. I have a report that is created in excel every month. I wish to create a command button that will bring it in. the excel sheet always starts with %com than the month and yr then ends with ad. an example would be %com0112ad.xls. I have created a form and have a text box. I want them to type in the 0112 and have the code supply the rest. At that point the code should bring in the excel sheet that starts with $com....take the text fields value and populate the 0112 than end the ad....looks like this I think....
I am using ACCESS 2010

%com & [frmImport].[txtdate] & ad...


any help would be appreciated

Micki

 

[PHV]

What is your actual code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Hoaokapohaku]

This will get you the file name you want:

Sub test()
Dim myInput As String
Dim myFile As String

myInput = InputBox("Enter whatever")
myFile = "%com" & myInput & "ad.xls"

End Sub

Since you already created a user form with a text box for input, replace the inputbox in the code above with reference to the user form text box. If you will use the "myFile" variable to open an already existing file with that name, edit the string above to include the whole directory path before the "%".

 

[MICKI0220]

PHV...I don't have code written because I thought I could import it with the DoCmd.TransferText function somehow.

 

[MICKI0220]

I put this in place and I thought it would get an error, but tried anyway. How can I make it see the variable Myfile as what is the file name...

 Private Sub cmdBefDel_Click()

Dim myInput As String
Dim myFile As String

    myInput = InputBox("Month and Year of Import")
    myFile = "%com" & myInput & "bd.xls"
    
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "BeforeWork", "K:\Transfer\myfile", True
    
    
    



End Sub

 

[PHV]

Perhaps this ?
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "BeforeWork", "K:\Transfer\" & myFile, True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MICKI0220]

I also tried this

Private Sub cmdBefDel_Click()

Dim myInput As String
Dim myFile As String

    myInput = InputBox("Month and Year of Import")
    myFile = "K:\Transfer\%com" & myInput & "bd.xls"
    
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "BeforeWork", "myfile", True
    
    
    



End Sub

 

[MICKI0220]

PHV,

I tried that and it can't find it. I then tried a fully qualified path and it still can't find it.

Private Sub cmdBefDel_Click()

Dim myInput As String
Dim myFile As String

    myInput = InputBox("Month and Year of Import")
    myFile = "%com" & myInput & "bd.xls"
    
  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "BeforeWork", "\\CTISERVER\Accounting\Transfer\" & myFile, True
    
        
End Sub

 

[TheAceMan1]

MICKI0220 . . .

What does [blue]myInput[/blue] look like?

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MICKI0220]

I figured it out. To test things I pasted the full path in and found errors in the way the file was named. Thanks PHV it worked right with the right syntax.