0

I met a problem when I want to write a VBA to copy the data from a specific path to another workbook

Sub Scorecard()
Dim Target_Workbook As Workbook
Dim Templete As Workbook
Dim Path As String
Dim a As Long
a = InputBox(Prompt:="Week End Date:", _
          Title:="Enter Your Week End Date ", Default:="")
Path = "C:\Users\Pudge\Desktop\Report\Scorecard\"&a"\Scorecard.xlsx"
Set Target_Workbook = Workbooks.Open(Path)
Set Template = ThisWorkbook
End Sub

I want to use the variable a to direct the path go different folder based on the a I provided;

Since In the folder Scorecard, there are many sub folders with name for example 1, 2,3,4,5; so what I want to do is if I give the vaule 1 for a, let go to open the scorecard.xlsx in the folder 1.

But it keeps give me compile error..

Please offer me a help if you have any idea.

Thx Pudge

Worst SQL Noob
  • 189
  • 1
  • 5
  • 15
  • 1
    There may be other issues but you are missing an ampersand after the 'a'. – SJR Dec 13 '16 at 18:58
  • `Path = "C:\Users\Pudge\Desktop\Report\Scorecard\" & a & "\Scorecard.xlsx"` – A.S.H Dec 13 '16 at 19:03
  • Now a geta compile error: syntax error for this path – Worst SQL Noob Dec 13 '16 at 19:18
  • Are you entering the date as, for example, `12/12/2016`. The slash is illegal in paths. Check your date is valid and then format as `dd-mmm-yyyy` - or any variation that doesn't include illegal characters. Saying that... just realised `a` is a long so is holding a number. http://stackoverflow.com/questions/1976007/what-characters-are-forbidden-in-windows-and-linux-directory-names – Darren Bartrup-Cook Dec 14 '16 at 10:26

1 Answers1

0

Firstly, there is an issue with your Path variable that has been solved above. I have mentioned that for the sake of providing a complete solution.

There is a spelling mistake at the beginning of your macro; you have dimensioned Templete but later referred to Template.

I believe your main issue most likely lies with your variable a. It is probably causing an incorrect / invalid path reference. There is no need to dimension this as Long; since you are concatenating it with other strings it is more logical to use String.

Please see the example code below:

Sub Scorecard()
Dim Target_Workbook, Template As Workbook
Dim Path, a As String
a = InputBox(Prompt:="Week End Date:", Title:="Enter Your Week End Date")
Path = "C:\Users\Pudge\Desktop\Report\Scorecard\" & a & "\Scorecard.xlsx"
Set Target_Workbook = Workbooks.Open(Path)
Set Template = ThisWorkbook
End Sub

Whilst I have not tested the above code, it should work. Experiment with alternative inputs; try using simple numbers to start with, and verify that the path is correct.

If that works, but your chosen application doesn't, there is likely an issue with your user input.

Scarfe
  • 400
  • 2
  • 13