Help Copying From Other Excel Workbooks

[GovNewbi]

Ok so I am BRAND NEW to programming in vb and pretty much brand new to programming period (with minimal experience in C++). My job has me writing a program which I have been told is NOT beginner programming and I am very lost on a few items. If there is anyone out there who can answer a few questions I would be eternally grateful!!!

I’ll start with a problem that I somewhat understand what the problem is…

In this part of the code I am trying to fetch information from other excel workbooks and copy the information into the workbook I am using. I realize this might be hard to follow because it is just a small chunk of a real long code and for that I am sorry. The error I am getting is

Compile Error:
Expected: list separator or )

It also highlights the word “Reporting” in the file location.

Here is part of the code…

Sub Fetch1()
Dim wb As Workbook
Dim ws As Worksheet
Dim CtCell As Range
Dim StGraphT As Range
Dim Proj As Range
Dim Yr As Range
Dim CT As Range
Dim CT2 As Range
Dim QRT As Range
Dim j As Integer
Dim CopyVal As String

Set CT = Range("CostType")
Set CT2 = Range("CostType2")
Set QRT = Range("Quarter")
Set Yr = Range("StYr")
Set Proj = Range("StProj").Offset(j, 0)
Set CtCell = Range("CtCell")
Set StGraphT = Range("StGraphT")
j = 0


Set ws = ActiveSheet
Set wb = Workbooks.Open("P:\ODD\OSD\BO\CARE Cost & Operations Forms _
- CONFIDENTIAL\CARE Reporting by OSR & CSR Project ID\OSR " _
& Proj.value & "\OSR" & Proj.value & " " & CT2.Value & "\OSR" & Proj.value _
& "_" & Yr.Value & "_" & QRT.Value & "_" & CT.Value & ".xls")
wb.Worksheets(SheetVal).Range(CopyVal).Copy
ws.Range("StFill").Offset(j, 0).Paste
wb.Close
j = j + 1
Loop

Application.ScreenUpdating = True

Call Graph1

End Sub


I know I have the location right so I am wondering if there is a simple error that I just can’t seem to see. A missing comma or something? PLZ HELP

 

[Andrzejek]

I have a little app that I wrote for myself that modifies this:

Select ABCD.Field1,   ABCD.Field2,  ABCD.Field3
From abcd.SomeOfMyTable ABCD,    anotherTable XYZ
Where ABCD.Field1  =    XYZ.Field9
And     some_other_stuff

into this:

"Select ABCD.Field1, ABCD.Field2, ABCD.Field3 " & vbNewLine _
& " From abcd.SomeOfMyTable ABCD, anotherTable XYZ " & vbNewLine _
& " Where ABCD.Field1 = XYZ.Field9 " & vbNewLine _
& " And some_other_stuff"

Some of my SQL statement are pretty long and it is a big time saver to use it. All vbNewLine helps me when I need to take existing SQL (from Debug.Print) and paste it into some other place.

Have fun.

---- Andy

 

[Andrzejek]

After an entire day of looking at the file location I realized I used and underscore to break up the line inside quotations

That proves my point of Debug.Print from my very first response to your post

Have fun.

---- Andy

 

[GovNewbi]

Still not totaly sure I understand this Debug.Print thing but I will def. be looking into it