Store file name input by user to be used in next line of macro?

[jcfraun]

I have a macro that imports data from a file and then manipulates the data. The TransferSpreadsheet options are set to:
Transfer Type: Import
Spreadsheet Type: Microsoft 8-10
Table Name: t_PPCGooglePhrases_WeeklyData_Imported
File Name: ="\\fileserv\shared\Public\Internet\Internet_Marketing\SEM_Reports\Weekly_Reporting\" & InputBox("Please enter data year.") & "\" & InputBox("Please enter name of file containing the Google weekly data.")
Has Field Names: Yes
Range: GooglePhrases!A5:J50000

So, what I want to be able to do is do another TransferSpreadsheet on the next line of the macro, but without having to ask for the filename again. It will be the same file name, but different range. How would I do this?

 

[Andrzejek]

Try something like:

Dim strDataYear As String
Dim strGoogleFile As String

strDataYear = InputBox("Please enter data year.")
strGoogleFile = InputBox("Please enter name of file containing the Google weekly data.")

...

File Name: ="\\fileserv\shared\Public\Internet\Internet_Marketing\SEM_Reports\Weekly_Reporting\" & strDataYear & "\" & strGoogleFile

Has Field Names: Yes
Range: GooglePhrases!A5:J50000

Then you can use [tt]strDataYear[/tt] and [tt]strGoogleFile[/tt] as variables in your procedure.

If you have many procedures and you want to use both of the variables in other places in your code, declare (Dim the variables) at the top of your code, just after [tt]Option Explicit[/tt] (which you should have at the top of your module).

Have fun.

---- Andy

 

[PHV]

Use VBA

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

 

[jcfraun]

Andy - Let me see if I understand (I'm fairly new to Access, but have been coding in Excel for a few years):

I now have a module called GetPaidFileName that has the following:

Option Compare Database
Option Explicit
Dim strDataYear As String
Dim strGoogleFile As String

Sub GetPaidFileName()
strDataYear = InputBox("Please enter data year.")
strPaidFile = InputBox("Please enter name of file containing the paid weekly data.")
End Sub

Can I then use these variables back in my macro or do I have to do the whole macro in VBA? I tried OpenModule, but it actually just opens the module. How can I get the sub to run from the macro?

Thanks,
Caryn

 

[PHV]

do I have to do the whole macro in VBA
I suggested that.
Have a look at the DoCmd.TransferSpreadsheet method.

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

 

[jcfraun]

PHV - yes, you did. Unfortunately, I'm not savvy enough with VBA to do this without significant struggle and I have a timeline. I would love to (and have no doubt that I will) get to the point that I am comfortable doing that.

I will have a look at that method. I also have the macro doing several other things with the data. If I can import the spreadsheets in VBA, can I get the code to run, followed by my macro to the remainder of the work?

 

[PHV]

DoCmd.RunMacro

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

 

[jcfraun]

Thanks, PHV. I admit that the DoCmd.TransferSpreadsheet was easy to do.

I will attempt to transfer it to VB. Thanks so much.

 

[jcfraun]

Two questions: I know have my code ready to go and it works when I run it from within VBA.

1.) I am running many queries from within the code (using DoCmd.OpenQuery). Three of these queries prompt for parameters and the parameter will always be the same, so I'm typing it in three times - any way to pass this value through the VBA code simply?

2.) I want to run this code from the switchboard - When I add a command to the switchboard, and say Run Code, it asks for the function (which it is not --- it is a Subroutine). I then can't get it to run. I come up with an error: There was an error executing the command.

Does it have to be a function?

Thanks!

 

[PHV]

these queries prompt for parameters
faq701-6763

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

 

[jcfraun]

PHV - You are a person of few words, but very helpful.

Thanks!