VBA Code with Problem on Variable filename

T

tina6587

New Member
Joined
Oct 17, 2008
Messages
10
Hi

I have written VBA code (Below). Which opens 3 other wb. Copies to selcted ws then creates a set of complete data with comprehensive information. This data will ultimately be used to print out documents in Crystal.

The wb opened are from variable paths. ie. O:\Transport\RSHEETS\Run Sheets nn\ nn nn\ nnnn nn nn nn.xls. Is one wb.

However where I have added a breakpoint, I cannot activate this wb even though I have used a Dim statement to refer to the wb. The first error occurs where I have highlighted red text, returning a message of 'Runtime error '9' subscript out of range.'

Can you help please?

Sub InsertCSVData()

'Get Run key and Delivery Date Information
Runk = InputBox("Enter Current Run Key (4 characters): ", "Input Run Key")
RSDate = InputBox("Enter Date ie dd mm yy (8 characters): ", "Input Date")
Dim rsheetfilename As String
Dim rsheetwindowname As String

'Open CSV Files Calls = Panasonic Run Sheet - Calls Written by Tina Slesser on 14/10/08

Workbooks.Open Filename:="O:\Transport\RSHEETS\Run Sheet Printing\CSV Files\panasonic run sheet - calls " + Runk + ".csv"
'CallsFilename = "O:\Transport\RSHEETS\Run Sheet Printing\CSV Files\panasonic run sheet - calls " + Runk + ".csv"
Workbooks.Open Filename:="O:\Transport\RSHEETS\Run Sheet Printing\CSV Files\panasonic run sheet - trips " + Runk + ".csv"
'TripsFilename = "O:\Transport\RSHEETS\Run Sheet Printing\CSV Files\panasonic run sheet - trips " + Runk + ".csv"
Workbooks.Open Filename:="O:\Transport\RSHEETS\RUN SHEET " + Mid(RSDate, 7, 2) + "\" + Mid(RSDate, 4, 2) + " " + Mid(RSDate, 7, 2) + "\" + Runk + " " + RSDate + ".xls "
rsheetfilename = "O:\Transport\RSHEETS\RUN SHEET " + Mid(RSDate, 7, 2) + "\" + Mid(RSDate, 4, 2) + " " + Mid(RSDate, 7, 2) + "\" + Runk + " " + RSDate + ".xls "
rsheetwindowname = Runk + " " + RSDate + ".xls "

'Open RSheetFilename For Random As #1
'Open CallsFilename For Random As #2
'Open TripsFilename For Random As #3

' Written on 14/10/2008 by Tina Slesser Ensures starting point for data import
Windows("Runsheet Print Master.xls").Activate
Sheets("Day Sheet Data").Select
Range("a1").Select
' InsertCSVData Macro
' Written on 14/10/2008 by Tina Slesser Copies all data from CSV files and Routesheet to Printing File
'
Range("A1").Select
'Windows("2390 23 09 08.xls").Activate
Windows("rsheetwindowname").Activate
 'RSheetFilename = "O:\Transport\RSHEETS\RUN SHEET " + Mid(RSDate, 7, 2) + "\" + Mid(RSDate, 4, 2) + " " + Mid(RSDate, 7, 2) + "\" + Runk + " " + RSDate + ".xls "
Cells.Select
Range("B1").Activate
Selection.Copy
Windows("Runsheet Print Master.xls").Activate
ActiveWindow.WindowState = xlNormal
Cells.Select
ActiveSheet.Paste
ActiveWindow.Zoom = 25
Selection.Interior.ColorIndex = xlNone
Selection.Font.ColorIndex = 0
Range("C4").Select
Sheets("CallsCSV").Select
Windows("Panasonic Run Sheet - Calls 2390.CSV").Activate
'"O:\Transport\RSHEETS\Run Sheet Printing\CSV Files\panasonic run sheet - calls " + Runk + ".csv"
Cells.Select
Selection.Copy
Windows("Runsheet Print Master.xls").Activate
ActiveWindow.WindowState = xlNormal
Range("A1").Select
ActiveSheet.Paste
Sheets("TripsCSV").Select
Windows("Panasonic Run Sheet - trips 2390.CSV").Activate
'"O:\Transport\RSHEETS\Run Sheet Printing\CSV Files\panasonic run sheet - trips " + Runk + ".csv"
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Windows("Runsheet Print Master.xls").Activate
ActiveWindow.WindowState = xlNormal
Range("A1").Select
ActiveSheet.Paste
Range("C13").Select
Sheets("Complete Data").Select
Range("A2").Select
Application.CutCopyMode = False
ActiveWorkbook.Save

'SetupCompleteData Macro
' Written on 14/10/2008 by Tina Slesser. Set up Complete data for printing
'
'
Sheets("Day Sheet Data").Select
Range("B1").Select
Selection.Copy
Sheets("Complete Data").Select
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleSingle
.ColorIndex = xlAutomatic
End With
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleSingle
.ColorIndex = xlAutomatic
End With
Selection.Font.Bold = False
Selection.Font.Underline = xlUnderlineStyleNone
With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("F2").Select
ActiveCell.FormulaR1C1 = ""
Cells.Select
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("F2").Select
ActiveCell.FormulaR1C1 = "=+CallsCSV!R[-1]C[-3]"
Range("G2").Select
ActiveCell.FormulaR1C1 = "+CallsCSV!g1"
Cells.Select
Selection.Font.Bold = True
Selection.Font.Bold = False
Selection.Font.Underline = xlUnderlineStyleSingle
Selection.Font.Underline = xlUnderlineStyleNone
Selection.NumberFormat = "General"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=CallsCSV!R[-1]C[-3]"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=CallsCSV!R[-1]C[-2]"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=CallsCSV!R[-1]C[-7]"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=CallsCSV!R[-1]C[-1]"
Range("K2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC6,'Day Sheet Data'!R[-1]:R[65534],19,FALSE)"
Selection.AutoFill Destination:=Range("K2:M2"), Type:=xlFillDefault
Range("K2:M2").Select
Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC6,'Day Sheet Data'!R[-1]:R[65534],20,FALSE)"
Range("M2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC6,'Day Sheet Data'!R[-1]:R[65534],16,FALSE)"
Range("K2").Select
Selection.Copy
Range("B2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC6,'Day Sheet Data'!R[-1]:R[65534],5,FALSE)"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:E2"), Type:=xlFillDefault
Range("B2:E2").Select
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC6,'Day Sheet Data'!R[-1]:R[65534],7,FALSE)"
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC6,'Day Sheet Data'!R[-1]:R[65534],6,FALSE)"
Range("E2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC6,'Day Sheet Data'!R[-1]:R[65534],3,FALSE)"
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC6,'Day Sheet Data'!R[-1]:R[65534],6,FALSE)"
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC6,'Day Sheet Data'!R[-1]:R[65534],8,FALSE)"
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC6,'Day Sheet Data'!R[-1]:R[65534],7,FALSE)"
Range("E2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC6,'Day Sheet Data'!R[-1]:R[65534],4,FALSE)"
Range("B2").Select
Sheets("Complete Data").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC6,'Day Sheet Data'!R[-1]:R[65534],5,FALSE)"
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC6,'Day Sheet Data'!R[-1]:R[65534],7,FALSE)"
Range("D2").Select
Sheets("Complete Data").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC6,'Day Sheet Data'!R[-1]:R[65534],6,FALSE)"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=+'Day Sheet Data'!R[-1]C[1]"
Range("A2").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Cells.Select
Cells.EntireColumn.AutoFit
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B3"), Type:=xlFillDefault
Range("B2:B3").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC6,'Day Sheet Data'!R1:R65536,5,FALSE)"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B3"), Type:=xlFillDefault
Range("B2:B3").Select
Range("B3").Select
Sheets("Day Sheet Data").Select
Columns("A:A").ColumnWidth = 26.14
Sheets("Complete Data").Select
Selection.AutoFill Destination:=Range("B3:B1577"), Type:=xlFillDefault
Range("B3:B1577").Select
ActiveWindow.LargeScroll Down:=-50
Range("C2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC6,'Day Sheet Data'!R1:R65536,7,FALSE)"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC6,'Day Sheet Data'!R1:R65536,6,FALSE)"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC6,'Day Sheet Data'!R1:R65536,4,FALSE)"
Range("K2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC6,'Day Sheet Data'!R1:R65536,19,FALSE)"
Range("L2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC6,'Day Sheet Data'!R1:R65536,20,FALSE)"
Range("M2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC6,'Day Sheet Data'!R1:R65536,16,FALSE)"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=+'Day Sheet Data'!R[-1]C2"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A3"), Type:=xlFillDefault
Range("A2:A3").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = "=+'Day Sheet Data'!R1C2"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A3"), Type:=xlFillDefault
Range("A2:A3").Select
Selection.AutoFill Destination:=Range("A2:A1870"), Type:=xlFillDefault
Range("A2:A1870").Select
ActiveWindow.LargeScroll Down:=-7
ActiveWindow.ScrollRow = 1
Range("F2:G2").Select
Selection.AutoFill Destination:=Range("F2:G1870"), Type:=xlFillDefault
Range("F2:G1870").Select
ActiveWindow.ScrollRow = 1
Range("H2:J2").Select
Selection.AutoFill Destination:=Range("H2:J1870"), Type:=xlFillDefault
Range("H2:J1870").Select
ActiveWindow.LargeScroll Down:=-13
ActiveWindow.ScrollRow = 1
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E3"), Type:=xlFillDefault
Range("E2:E3").Select
Selection.AutoFill Destination:=Range("E2:E6"), Type:=xlFillDefault
Range("E2:E6").Select
Selection.AutoFill Destination:=Range("E2:E1870"), Type:=xlFillDefault
Range("E2:E1870").Select
ActiveWindow.ScrollRow = 1

Range("C2:D2").Select
Selection.AutoFill Destination:=Range("C2:D1870"), Type:=xlFillDefault
Range("C2:D1870").Select
ActiveWindow.LargeScroll Down:=-10
ActiveWindow.SmallScroll Down:=21
Range("B1576").Select
Selection.AutoFill Destination:=Range("B1576:B1870"), Type:=xlFillDefault
Range("B1576:B1870").Select
ActiveWindow.LargeScroll Down:=-4
ActiveWindow.ScrollRow = 1
Range("K2:M2").Select
Selection.AutoFill Destination:=Range("K2:M1870"), Type:=xlFillDefault
Range("K2:M1870").Select
Range("H1860").Select
Range("K1851").Select
ActiveWindow.ScrollRow = 1
Range("D1840").Select
Cells.Select
Range("A2").Activate
Cells.EntireColumn.AutoFit
ActiveWindow.LargeScroll ToRight:=1
Range("M22").Select
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=60
ActiveWorkbook.Save

' Written on 14/10/2008 by Tina Slesser Close files after copying data to Printing Runsheet File
'
'RSheetFilename = "O:\Transport\RSHEETS\RUN SHEET " + Mid(RSDate, 7, 2) + "\" + Mid(RSDate, 4, 2) + " " + Mid(RSDate, 7, 2) + "\" + Runk + " " + RSDate + ".xls ".Activate
Windows("2390 23 09 08.xls").Activate
ActiveWindow.Close
'Windows("O:\Transport\RSHEETS\Run Sheet Printing\CSV Files\panasonic run sheet - trips " + Runk + ".csv").Activate
Windows("Panasonic Run Sheet - trips 2390.CSV").Activate
ActiveWindow.Close
'Windows("O:\Transport\RSHEETS\Run Sheet Printing\CSV Files\panasonic run sheet - calls " + Runk + ".csv").Activate
Windows("Panasonic Run Sheet - calls 2390.CSV").Activate
ActiveWindow.Close

End Sub
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
EDIT: Welcome to the Board... but still...

YIKES

when posting up mammoth code try to encase within CODE tags which makes it slightly easier to read through... else people will just run a mile. CODE tags are as follows

before first line of code: [*CODE*]
after last line of code: [*/CODE*]

Without the asterix -- used so as not to actually generate the CODE tags in this instance!

I've not read through in full but this:

Code: 
Windows("rsheetwindowname").Activate

Should be:

Code: 
Windows(rsheetwindowname).Activate

As rsheetwindowname is a variable you do not encase within quotation marks... you would use quotation marks if you were actually entering the file name directly, eg:

Code: 
Windows("Book2.xls").Activate

HTH
 
Upvote 0
Gee thanks for your prompt reply, welcome and guidance.

It's the first time I have joined an IT forum, so appreciate your assistance.

I tried without quotations but I still receive the same error message. What is strange is if I hover over it. I see it is looking at the correct file. ie. rsheetwindowname = "2390 23 09 08.xls"
 
Upvote 0
Try changing:

Code: 
rsheetfilename = "O:\Transport\RSHEETS\RUN SHEET " + Mid(RSDate, 7, 2) + "\" + Mid(RSDate, 4, 2) + " " + Mid(RSDate, 7, 2) + "\" + Runk + " " + RSDate + ".xls "
rsheetwindowname = Runk + " " + RSDate + ".xls "

To

Code: 
rsheetfilename = ActiveWorkbook.FullName
rsheetwindowname = ActiveWorkbook.Name

This should just ensure that both variables are exactly as per the opened file (when you open a file by default it becomes the active workbook so this a common method to ascertaining names etc... .FullName being inclusive of path and .Name being "shorthand")

HTH
 
Upvote 0
All I can say is that you are an absolute star.


Thankyou, thankyou, thankyou!!!!!!

I have tried for 3 days to put this right.

I have another problem and wonder if you would also be able to assist with this as well? I need to write code to copy data but need to first figure out how to explain it in text. Would you be able to assist with that too?
 
Upvote 0
OK, I sure hope this makes sense.......

I have a wb with 4 ws, ws4 is a result of combined data from ws1,2,3

I have copied data from ws2 & 3 and VLOOKUP for data from ws1.

ws1 also has data that does not have a unique identifer as per previous VLOOKUP. However, I am sure I can copy this data using a formula that states if the data is not already present in column then insert where unique identifer is found or add to bottom if not.

I'm not sure if this makes absolute sense.

If any assistance can be given you would be a life saver ;););)
 
Upvote 0
The VLOOKUP is:
Code: 
=VLOOKUP($F2,'Day Sheet Data'!$1:$65536,5,FALSE)
which looks at the unique identifer within the CSV worksheets
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Loop/Repeat Macro
2
Replies
11
Views
531
kvsrinivasamurthy
K
P
  • Question
Help with Vba Code
Replies
3
Views
253
Coyotex3
C
L
  • Question
Variable Rows, Help Speeding Up
Replies
4
Views
396
kevin9999
K
L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
1K
Joe4
Joe4
D
  • Question
VBA optimization advice
Replies
3
Views
239
bobsan42
bobsan42

Forum statistics

Threads
1,217,440
Messages
6,136,639
Members
450,022
Latest member
Joel1122331

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top